﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using HZ.Data;
using HZ.DrugTrade.Model;
using Oracle.DataAccess.Client;

namespace HZ.DrugTrade.DAL
{
    public class DAL_PurchaseOrderDetail
    {

        /// <summary>
        /// 分页获取出库明细信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.PurchaseOrderDetail> GetPurchaseOrderDetail_Page(HZ.DrugTrade.Model.Query.PurchaseOrderDetail where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_DistributionRelation WHERE 1=1 {0} ", where), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.PurchaseOrderDetail>(dt);
        }

        /// <summary>
        /// 批量插入采购单明细
        /// </summary>
        /// <returns>0失败1成功3表示采购单已经提交不能再添加</returns>
        public static int AddPurchaseOrderDetail_Batch(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList)
        {
            if (PurchaseOrderDetailList.Count == 0)
            {
                return 1;

            }

            /* 
            // 测试代码
            for (int i = 0; i < PurchaseOrderDetailList.Count; i++)
            {

                if (PurchaseOrderDetailList[i].OrderDetailID == null)
                {
                    PurchaseOrderDetailList[i].OrderDetailID = Guid.NewGuid().ToString();
                    PurchaseOrderDetailList[i].ExecuteTime = DateTime.Now;
                }
            }


            var idb = HZ.Data.DbHelperFactory.Create();

            try
            {
                idb.BeginTrans();
                //

                foreach (var item in PurchaseOrderDetailList)
                {
                    HZ.Data.DAL_Helper.OperateTableSQL(item, TableOperator.Insert, idb);
                }
                // 计算总金额

                string sql = @"update tb_purchaseorder set totalamount = (select sum(price * purchasecount) sum from tb_purchaseorderdetail
where tb_purchaseorderdetail.orderid = tb_purchaseorder.orderid and tb_purchaseorder.orderid = '{0}')
where  tb_purchaseorder.orderid = '{0}'";
                idb.ExecuteNonQuery(string.Format(sql, PurchaseOrderDetailList[0].OrderID));
                idb.CommitTrans();

            }
            catch (Exception ee)
            {
                idb.RollTrans();

                throw ee;
            }
            return 1;

            */
            int Flag = 0;
            //Oracle.DataAccess.Client.OracleConnection connection = DAL_OracleHelper.OracleConnection();
            Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection(HZ.Data.DbHelperFactory.Create().ConnectionString);
            Oracle.DataAccess.Client.OracleCommand cmd = DAL_OracleHelper.OracleCommand();
            try
            {
                //填充表参数
                TBADDORDERDETAIL dt = new TBADDORDERDETAIL();
                List<ADDORDERDETAIL> NewList = new List<ADDORDERDETAIL>();
                for (int i = 0; i < PurchaseOrderDetailList.Count; i++)
                {
                    ADDORDERDETAIL newobj = new ADDORDERDETAIL();
                    newobj.ORDERDETAILID = Guid.NewGuid().ToString();
                    newobj.ORDERID = PurchaseOrderDetailList[i].OrderID;
                    newobj.PRICE = PurchaseOrderDetailList[i].Price.ToString();
                    newobj.PROCURECATALOGID = PurchaseOrderDetailList[i].ProcureCatalogID;
                    newobj.COMPANYID_PS = PurchaseOrderDetailList[i].CompanyID_PS;
                    newobj.PURCHASECOUNT = PurchaseOrderDetailList[i].PurchaseCount.GetValueOrDefault(0).ToString();
                    newobj.AMOUNT = (PurchaseOrderDetailList[i].PurchaseCount * PurchaseOrderDetailList[i].Price).ToString();
                    NewList.Add(newobj);
                }
                dt.Value = NewList.ToArray();
                //打开连接
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "usp_POrderDetailBatchAdd";//存储过程名字

                Oracle.DataAccess.Client.OracleParameter[] pars = new Oracle.DataAccess.Client.OracleParameter[3];
                pars[0] = new Oracle.DataAccess.Client.OracleParameter("V_OrderID", PurchaseOrderDetailList[0].OrderID);
                pars[1] = new Oracle.DataAccess.Client.OracleParameter("v_recUserInfo", Oracle.DataAccess.Client.OracleDbType.Object);
                pars[1].UdtTypeName = "TBADDORDERDETAIL";
                pars[1].Value = dt;
                pars[2] = new Oracle.DataAccess.Client.OracleParameter("out_success", Oracle.DataAccess.Client.OracleDbType.Int32);
                pars[2].Direction = ParameterDirection.Output;
                cmd.Parameters.Add(pars[0]);
                cmd.Parameters.Add(pars[1]);
                cmd.Parameters.Add(pars[2]);
                cmd.ExecuteNonQuery();
                Flag = int.Parse(pars[2].Value.ToString());
            }
            catch (Exception e)
            {
                Flag = 0;
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
                connection.Dispose();
            }
            return Flag;


        }



        #region 流程首节点缓存相关

        /// <summary>
        /// Lazy Load机制的流程首节点缓存。
        /// </summary>
        private static IDictionary<string, string> _FlowCache = null;
        /// <summary>
        /// 获取流程首节点缓存
        /// </summary>
        public static IDictionary<string, string> FlowCache
        {
            get
            {
                if (_FlowCache == null)
                {
                    IDataReader dr = HZ.Data.DbHelperFactory.Create().ExecuteReader("SELECT f.FlowID, n.NodeID FROM TB_Flow AS f INNER JOIN  TB_FlowNode AS n ON f.FlowID = n.FlowID WHERE n.FatherNodeID IS NULL ");
                    if (dr.Read())
                    {
                        _FlowCache = new Dictionary<string, string>(16);
                        do
                        {
                            _FlowCache[dr["FlowID"].ToString()] = dr["NodeID"].ToString();
                        }
                        while (dr.Read());
                    }
                    dr.Close();
                }
                return _FlowCache;
            }
            set
            {
                //清空首节点缓存
                _FlowCache = null;
            }

        }
        #endregion


        /// <summary>
        /// 批量导入采购单明细
        /// 采购独立存储过程，速度优化
        /// </summary>
        /// <returns>0失败1成功</returns>
        public static int ImportPurchaseOrderDetail_Batch(string orderID, IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_OrderID", orderID),
                new SqlParameter("@in_ProcureCatalogIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"ProcureCatalogID"  ,strSplit)),
                new SqlParameter("@in_PurchaseCounts",HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"PurchaseCount"  ,strSplit)),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Batch_Import", paras);

            return (int)paras[paras.Length - 1].Value;
        }


        /// <summary>
        /// 批量更新采购数量
        /// </summary>
        public static int UpdatePurchaseOrderDetail_PurchaseCount_Batch(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"OrderDetailID"  ,strSplit)),
            //    new SqlParameter("@in_PurchaseCounts",HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"PurchaseCount"  ,strSplit)),
            //    new SqlParameter("@in_Amounts",HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"Amount"  ,strSplit)),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Batch_Update", paras);

            //return (int)paras[paras.Length - 1].Value;
            if (PurchaseOrderDetailList.Count == 0)
            {
                return 1;
            }
            int Flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string IsSubmitSql = string.Format(" SELECT OrderStatus FROM TB_PurchaseOrder WHERE  OrderID = '{0}'", PurchaseOrderDetailList[0].OrderID);
                if (int.Parse(h.ExecuteScalar(IsSubmitSql).ToString()) == 0)
                {
                    foreach (var item in PurchaseOrderDetailList)
                    {
                        DateTime time = Convert.ToDateTime(h.ExecuteScalar(string.Format(@"select MODIFYTIME from TB_PurchaseOrderDetail where OrderDetailID = '{0}'", item.OrderDetailID)));
                        if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                        {
                            h.ExecuteNonQuery(string.Format("  UPDATE TB_PurchaseOrderDetail SET PurchaseCount = {0},Amount = {1},MODIFYTIME=sysdate,MODIFYSTATUS=1   WHERE  OrderDetailID = '{2}'", item.PurchaseCount, item.Amount, item.OrderDetailID));
                        }
                        else
                        {
                            h.ExecuteNonQuery(string.Format("  UPDATE TB_PurchaseOrderDetail SET PurchaseCount = {0},Amount = {1}  WHERE  OrderDetailID = '{2}'", item.PurchaseCount, item.Amount, item.OrderDetailID));
                        }
                    }
                    DateTime times = Convert.ToDateTime(h.ExecuteScalar(string.Format(@"select MODIFYTIME from TB_PurchaseOrder where OrderId = '{0}'", PurchaseOrderDetailList[0].OrderID)));
                    if (times.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || times == null)
                    {
                        h.ExecuteNonQuery(string.Format("UPDATE TB_PurchaseOrder SET TotalAmount = (SELECT  SUM(Amount) FROM   TB_PurchaseOrderDetail WHERE  OrderID ='{0}'),MODIFYTIME=sysdate,MODIFYSTATUS=1  WHERE  OrderID ='{0}'", PurchaseOrderDetailList[0].OrderID));
                    }
                    else
                    {
                        h.ExecuteNonQuery(string.Format("UPDATE TB_PurchaseOrder SET TotalAmount = (SELECT  SUM(Amount) FROM   TB_PurchaseOrderDetail WHERE  OrderID ='{0}') WHERE  OrderID ='{0}'", PurchaseOrderDetailList[0].OrderID));
                    }
                }
                else
                {
                    h.CommitTrans();
                    return 0;
                }
                h.CommitTrans();
                Flag = 1;
            }
            catch
            {
                h.RollTrans();
                Flag = 0;
            }
            return Flag;

        }

        /// <summary>
        /// 批量企业确认采购单明细
        /// </summary>
        /// <param name="PurchaseOrderDetailList">采购单明细列表</param>
        /// <param name="flag">True确认，False取消</param>
        /// <returns></returns>
        public static int ConfirmPurchaseOrderDetail_Batch(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList, int flag)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"OrderDetailID"  ,strSplit)), 
            //    new SqlParameter("@in_flag", flag), 
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Batch_Confirm", paras);

            //return (int)paras[paras.Length - 1].Value;

            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                string sqlConfirm = @" UPDATE  TB_PurchaseOrderDetail 
                                                        SET     OrderDetailState  = @in_flag
                                                        WHERE   OrderDetailID  = @in_OrderDetailID";

                foreach (var item in PurchaseOrderDetailList)
                {
                    List<string> Confirmparasnames = new List<string>();
                    List<object> Confirmvalues = new List<object>();

                    Confirmparasnames.Add("@in_OrderDetailID");
                    Confirmvalues.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                    Confirmparasnames.Add("@in_flag");
                    Confirmvalues.Add(flag);

                    h.ExecuteNonQuery(CommandType.Text, sqlConfirm, HZ.Data.DBParameterFactory.GetIDbDataParameterList(Confirmparasnames, Confirmvalues).ToArray());

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug(typeof(DAL_User).FullName, "企业确认采购单明细不成功", ex);
                retValue = 0;
            }

            return retValue;
        }


        /// <summary>
        /// 批量企业确认采购单明细:可添加拒绝配送理由
        /// </summary>
        /// <param name="PurchaseOrderDetailList">采购单明细列表</param>
        /// <param name="flag"></param>
        /// <returns></returns>
        public static int ConfirmPurchaseOrderDetail_Batch2(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList, int flag)
        {
            //            HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
            //            try
            //            {
            //                h.BeginTrans();


            //                string strSql = string.Empty;

            //                foreach (Model.PurchaseOrderDetail item in PurchaseOrderDetailList)
            //                {
            //                    strSql = string.Format(@"UPDATE [TB_PurchaseOrderDetail]
            //		        SET   [OrderDetailState] = {0}	,[Reason]='{1}'                
            //		        WHERE  [OrderDetailID] ='{2}' ;", flag, item.Reason, item.OrderDetailID);
            //                    h.ExecuteNonQuery(strSql);
            //                }

            //                h.CommitTrans();
            //                return 1;
            //            }
            //            catch (Exception ee)
            //            {
            //                h.RollTrans();
            //                HZ.Common.log4netManager.LogDebug("ConfirmPurchaseOrderDetail_Batch2", ee.Message, ee);
            //                return 0;
            //            }

            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                 string sql = string.Format(@"select count(*) from TB_PurchaseOrderDetail where OrderDetailID  = @in_OrderDetailID and OrderDetailState in (2,3)");
                int count = 0;
                foreach(var items in PurchaseOrderDetailList)
                {
                    count = Convert.ToInt32(h.ExecuteScalar(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", items.OrderDetailID)));
                }
                if (count > 0)
                {
                    return 2;
                }
                else
                {
                    string sqlConfirm = @" UPDATE TB_PurchaseOrderDetail
                                         SET   OrderDetailState = @in_OrderDetailState	,Reason=@in_Reason                
                                         WHERE  OrderDetailID =@in_OrderDetailID ";

                    foreach (var item in PurchaseOrderDetailList)
                    {
                        List<string> Confirmparasnames = new List<string>();
                        List<object> Confirmvalues = new List<object>();

                        Confirmparasnames.Add("@in_OrderDetailState");
                        Confirmvalues.Add(flag);

                        Confirmparasnames.Add("@in_Reason");
                        Confirmvalues.Add(string.IsNullOrEmpty(item.Reason) ? "" : item.Reason.Trim());

                        Confirmparasnames.Add("@in_OrderDetailID");
                        Confirmvalues.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                        h.ExecuteNonQuery(CommandType.Text, sqlConfirm, HZ.Data.DBParameterFactory.GetIDbDataParameterList(Confirmparasnames, Confirmvalues).ToArray());

                    }

                    h.CommitTrans();
                    retValue = 1;
                }
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("ConfirmPurchaseOrderDetail_Batch2", ex.Message, ex);
                retValue = 0;
            }

            return retValue;

        }

        /// <summary>
        /// 批量提交采购单明细
        /// </summary>
        /// <returns>0失败1成功</returns>
        public static int UpdatePurchaseOrderDetail_Submit(HZ.DrugTrade.Model.PurchaseOrder purchaseOrder)
        {
            int flag = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                //    IDataParameter[] paras = new IDataParameter[] { 
                //    new SqlParameter("@in_OrderID", purchaseOrder.OrderID),
                //    new SqlParameter("@out_Success",SqlDbType.Int)
                //};

                //    paras[paras.Length - 1].Direction = ParameterDirection.Output;
                //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Batch_Submit", paras);

                //    flag = (int)paras[paras.Length - 1].Value;
                string IsHavePurchaseOrderDetailsql = string.Format(" SELECT count(*)  FROM TB_PurchaseOrderDetail WHERE   OrderID='{0}'", purchaseOrder.OrderID);
                string IsSubmitsql = string.Format(" SELECT OrderStatus FROM TB_PurchaseOrder WHERE   OrderID = '{0}'", purchaseOrder.OrderID);
                //判断采购单是不是空单 和采购单是否提交
                if (int.Parse(h.ExecuteScalar(IsHavePurchaseOrderDetailsql).ToString()) > 0 && int.Parse(h.ExecuteScalar(IsSubmitsql).ToString()) == 0)
                {
                    //提交采购单 和采购单详情
                    string sql = string.Format(@"  begin 
                             UPDATE  TB_PurchaseOrder       SET  OrderStatus = 1,SUBMITTIME=SYSDATE   WHERE   OrderID = '{0}';
                             UPDATE  TB_PurchaseOrderDetail SET  OrderDetailState = 1  WHERE   OrderID = '{0}';
                          end;", purchaseOrder.OrderID);
                    h.ExecuteNonQuery(sql);
                }
                else
                {
                    //h.CommitTrans();
                    return 2;

                }
                h.CommitTrans();
                flag = 1;
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_PurchaseOrderDetail", ee.Message, ee);
                h.RollTrans();
                flag = 0;
            }
            return flag;
        }


        /// <summary>
        /// 批量删除采购单明细
        /// </summary>
        /// <returns>0失败1成功</returns>
        public static int DeletePurchaseOrderDetail_Batch(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> PurchaseOrderDetailList)
        {
            if (PurchaseOrderDetailList.Count == 0)
            {
                return 1;
            }
            int flag = 0;

            var h = HZ.Data.DbHelperFactory.Create();

            try
            {
                //    string strSplit = ",";
                //    IDataParameter[] paras = new IDataParameter[] { 
                //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(PurchaseOrderDetailList,"OrderDetailID"  ,strSplit)),
                //    new SqlParameter("@out_Success",SqlDbType.Int)
                //};
                //    paras[paras.Length - 1].Direction = ParameterDirection.Output;

                //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Batch_Del", paras);

                //    flag = (int)paras[paras.Length - 1].Value; 
                h.BeginTrans();
                string sql = string.Format(" SELECT count(*) FROM TB_PurchaseOrder WHERE  OrderID = (select distinct OrderID from TB_PurchaseOrderDetail where orderdetailid='{0}')", PurchaseOrderDetailList[0].OrderDetailID);
                int count = Convert.ToInt32(h.ExecuteScalar(sql));
                if (count == 0)
                {
                    return 3;
                }
                else
                {
                    string IsSubmitSql = string.Format(" SELECT OrderStatus FROM TB_PurchaseOrder WHERE  OrderID = (select distinct OrderID from TB_PurchaseOrderDetail where orderdetailid='{0}')", PurchaseOrderDetailList[0].OrderDetailID);
                    if (int.Parse(h.ExecuteScalar(IsSubmitSql).ToString()) == 0)
                    {
                        StringBuilder sb = new StringBuilder();
                        sb.AppendLine(" declare V_OrderID varchar2(36);");
                        sb.AppendLine(" begin ");
                        sb.AppendLine(string.Format(" select  OrderID into V_OrderID from TB_PurchaseOrderDetail where orderdetailid='{0}';", PurchaseOrderDetailList[0].OrderDetailID));
                        foreach (var item in PurchaseOrderDetailList)
                        {
                            sb.AppendLine(string.Format("insert into  TB_PurchaseOrderDetail_DEL  select * from TB_PurchaseOrderDetail WHERE OrderDetailID = '{0}' ;", item.OrderDetailID));
                            sb.AppendLine(string.Format("update TB_PurchaseOrderDetail_DEL set MODIFYTIME =sysdate WHERE OrderDetailID = '{0}' ;", item.OrderDetailID));
                            sb.AppendLine(string.Format("DELETE  FROM TB_PurchaseOrderDetail WHERE OrderDetailID = '{0}' ;", item.OrderDetailID));
                        }
                        DateTime times = Convert.ToDateTime(h.ExecuteScalar(string.Format(@"select MODIFYTIME from TB_PurchaseOrder where OrderId in (select orderid from TB_PurchaseOrderDetail where orderdetailid = '{0}')", PurchaseOrderDetailList[0].OrderDetailID)));
                        if (times.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || times == null)
                        {
                            sb.AppendLine("UPDATE TB_PurchaseOrder   SET TotalAmount = (SELECT  SUM(Amount) FROM   TB_PurchaseOrderDetail WHERE  OrderID =V_OrderID),MODIFYTIME=sysdate,MODIFYSTATUS=1  where OrderID=V_OrderID;");
                        }
                        else
                        {
                            sb.AppendLine("UPDATE TB_PurchaseOrder   SET TotalAmount = (SELECT  SUM(Amount) FROM   TB_PurchaseOrderDetail WHERE  OrderID =V_OrderID) where OrderID=V_OrderID;");
                        }
                        sb.AppendLine(" end; ");
                        h.ExecuteNonQuery(sb.ToString());
                        flag = 1;
                    }
                    else
                    {

                        return 2;
                    }
                    h.CommitTrans();
                }
            }
            catch (Exception ee)
            {
                h.RollTrans();
                HZ.Common.log4netManager.LogDebug("DAL_PurchaseOrderDetail", ee.Message, ee);
                flag = 0;
            }
            return flag;
        }

        /// <summary>
        /// 批量更新采购单明细配送信息(0.不成功,1.成功,)
        /// </summary>
        /// <returns>0失败1成功</returns>
        public static int UpdatePurchaseOrderDetail_Distribution_Batch(IList<HZ.DrugTrade.Model.Distribution> DistributionList, IList<HZ.DrugTrade.Model.DistributionBatchRecord> DistributionBatchRecordList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"OrderDetailID"  ,strSplit)),
            //    new SqlParameter("@in_DistributeCounts", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributeCount"  ,strSplit)),
            //    new SqlParameter("@in_BatchRecordIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionBatchRecordList,"BatchRecordID"  ,strSplit)),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Distribution_Batch_Update", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43

            #region updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                for (var i = 0; i < DistributionList.Count; i++)
                {
                    var item = DistributionList[i];
                    string strsql = string.Format("select count(*) from view_PurchaseOrderDetail where orderdetailstate = 0 and OrderDetailID = @in_OrderDetailID");
                    int count = Convert.ToInt32(h.ExecuteScalar(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID)));
                    if (count > 0)
                    {
                        return 2;
                    }
                    else
                    {
                        string sql = string.Format(@"select count(*) from TB_PurchaseOrderDetail where OrderDetailID  = @in_OrderDetailID and OrderDetailState in (2,3)");
                        int counts = Convert.ToInt32(h.ExecuteScalar(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID)));
                        if (counts > 0)
                        {
                            return 3;
                        }
                        else
                        {
                            string strCheck = @"SELECT  PurchaseCount, DistributeCount,HospitalID,HospitalName,CompanyID_PS,CompanyName_PS,
                                                ProcureCatalogID,AreaID,AreaName,OrderID,Price,OrderDetailState
                                                    FROM   TB_PurchaseOrderDetail
                                                    WHERE  OrderDetailID = @in_OrderDetailID  for update";

                            DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));

                            int PurchaseCount = int.Parse(dt.Rows[0]["PurchaseCount"].ToString());
                            int DistributeCount = int.Parse(dt.Rows[0]["DistributeCount"].ToString());
                            string hospitalID = dt.Rows[0]["HospitalID"].ToString();
                            string hospitalName = dt.Rows[0]["HospitalName"].ToString();
                            string companyID_PS = dt.Rows[0]["CompanyID_PS"].ToString();
                            string companyName_PS = dt.Rows[0]["CompanyName_PS"].ToString();
                            string procurecatalogID = dt.Rows[0]["ProcureCatalogID"].ToString();
                            string areaID = dt.Rows[0]["AreaID"].ToString();
                            string areaName = dt.Rows[0]["AreaName"].ToString();
                            string orderID = dt.Rows[0]["OrderID"].ToString();
                            double price = double.Parse(dt.Rows[0]["Price"].ToString());
                            //int orderDetailState = int.Parse(dt.Rows[0]["OrderDetailState"].ToString());
                            int orderDetailState = 2;  //因取消订单确认步骤，直接配送时，配送表中的 订单状态更新为已确认状态 add by ysm 20130201

                            //判断 配送总量是否大于实际采购量，大于则回滚
                            if (DistributeCount + item.DistributeCount > PurchaseCount)
                            {
                                new Exception("配送总量大于实际采购量");
                            }
                            else
                            {
                                #region 新增配送记录


                                /*
                         * 添加配送单        
                         */
                                string DistributionSerialID = System.Guid.NewGuid().ToString();

                                string sqlInsertDistribution = string.Format(@" INSERT INTO  TB_Distribution 
                                                                          (
                                                                            DistributionSerialID 
                                                                           ,OrderDetailID 
                                                                           ,DistributeCount 
                                                                           ,DistributeTime 
                                                                           ,DistributeState 
                                                                           ,WarehouseCount 
                                                                           ,WarehouseTime 
                                                                           ,IsInvoice 
                                                                           ,HospitalID 
                                                                           ,HospitalName 
                                                                           ,CompanyID_PS 
                                                                           ,CompanyName_PS 
                                                                           ,ProcureCatalogID 
                                                                           ,BatchRecordID 
                                                                           ,AreaID
                                                                           ,AreaName 
                                                                           ,OrderID 
                                                                           ,PurchaseCount 
                                                                           ,Price 
                                                                           ,OrderDetailState
                                                                           ,MODIFYTIME
                                                                           ,MODIFYSTATUS
                                                                           ,UPLOADSTATUS 
                                                                          )
                                                                            VALUES
                                                                          (
                                                                            '{0}'
                                                                           ,@in_OrderDetailID
                                                                           ,@in_DistributeCount
                                                                           ,sysdate
                                                                           ,0
                                                                           ,0
                                                                           ,NULL
                                                                           ,0
                                                                           ,'{1}'
                                                                           ,'{2}'
                                                                           ,'{3}'
                                                                           ,'{4}' 
                                                                           ,'{5}' 
                                                                           ,'{6}' 
                                                                           ,'{7}' 
                                                                           ,'{8}'       
                                                                           ,'{9}' 
                                                                           ,{10}             
                                                                           ,{11} 
                                                                           ,{12} 
                                                                           ,sysdate
                                                                           ,0
                                                                           ,0
                                                                          )", DistributionSerialID, hospitalID, hospitalName, companyID_PS, companyName_PS, procurecatalogID, DistributionBatchRecordList[i].BatchRecordID
                                                                                    , areaID, areaName, orderID, PurchaseCount, price, orderDetailState);

                                List<string> parasnames = new List<string>();
                                List<object> values = new List<object>();

                                parasnames.Add("@in_OrderDetailID");
                                values.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                                parasnames.Add("@in_DistributeCount");
                                values.Add(item.DistributeCount);

                                h.ExecuteNonQuery(CommandType.Text, sqlInsertDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                                #endregion

                                //                        var item2 = DistributionBatchRecordList[i];
                                //                        #region 新增配送批号信息  注释 by zdc 性能升级后

                                //                        /*
                                //                         * 添加配送批号        
                                //                         */
                                //                        string sqlInsertDistributionBatchRecord = string.Format(@" INSERT INTO TB_DistributionBatchRecord
                                //                                                                                  (
                                //                                                                                    DistributionSerialID
                                //                                                                                   ,BatchRecordID
                                //                                                                                  )
                                //                                                                                    VALUES
                                //                                                                                  (
                                //                                                                                    '{0}'
                                //                                                                                   ,@in_BatchRecordID
                                //                                                                                  )", DistributionSerialID);


                                //                        List<string> parasnames2 = new List<string>();
                                //                        List<object> values2 = new List<object>();

                                //                        parasnames2.Add("@in_BatchRecordID");
                                //                        values2.Add(item2.BatchRecordID);


                                //                        h.ExecuteNonQuery(CommandType.Text, sqlInsertDistributionBatchRecord, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                                //                        #endregion

                                #region 汇总配送数量

                                /*
                            * 汇总配送量        
                        */
                                string strGetSUMDistributeCount = @"SELECT nvl(SUM(nvl(DistributeCount,0)),0) SUMDistributeCount
                                                                FROM   TB_Distribution
                                                                WHERE  OrderDetailID = @in_OrderDetailID";


                                /*
                                 * 更新配送量   modfiy by ysm 20130201 去掉订单确认步骤，若配送，则更新订单状态为已确认
                                 */
                                string sqlSUMDistributeCount = @" 
                                                        UPDATE TB_PurchaseOrderDetail
                                                        SET    DistributeCount = @in_DistributeCount,OrderDetailState = 2 
                                                        where OrderDetailID= @in_OrderDetailID";


                                DataTable dt2 = h.ExecuteTable(CommandType.Text, strGetSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));

                                int SUMDistributeCount = int.Parse(dt2.Rows[0]["SUMDistributeCount"].ToString());


                                List<string> parasnames3 = new List<string>();
                                List<object> values3 = new List<object>();

                                parasnames3.Add("@in_DistributeCount");
                                values3.Add(SUMDistributeCount);

                                parasnames3.Add("@in_OrderDetailID");
                                values3.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                                h.ExecuteNonQuery(CommandType.Text, sqlSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                                /*
                                 * 更新配送单冗余（采购明细配送总数）  add by zdc
                                 */
                                //采购明细配送总数
                                int tem_DistributeCount = 0;
                                string sqlResult = string.Format(@"SELECT SUM(DistributeCount)
                                                        FROM   TB_Distribution
                                                        WHERE  OrderDetailID = @in_OrderDetailID group by OrderDetailID");
                                string Distcount3 = h.ExecuteScalar(CommandType.Text, sqlResult, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID)).ToString();
                                tem_DistributeCount = int.Parse(Distcount3);
                                string sqlTotalDistributeCount = @" 
                                                        UPDATE TB_Distribution
                                                        SET    TotalDistributeCount = @in_DistributeCount
                                                        where OrderDetailID= @in_OrderDetailID";
                                List<string> parasnames4 = new List<string>();
                                List<object> values4 = new List<object>();

                                parasnames4.Add("@in_DistributeCount");
                                values4.Add(tem_DistributeCount);

                                parasnames4.Add("@in_OrderDetailID");
                                values4.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                                h.ExecuteNonQuery(CommandType.Text, sqlTotalDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames4, values4).ToArray());
                                #endregion
                            }
                        }
                    }

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("UpdatePurchaseOrderDetail_Distribution_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion
        }

        /// <summary>
        /// 批量更新采购单明细配送信息(0.不成功,1.成功,)
        /// </summary>
        /// <returns>0失败1成功</returns>
        public static int EditPurchaseOrderDetail_Distribution_Batch(IList<HZ.DrugTrade.Model.Distribution> DistributionList, IList<HZ.DrugTrade.Model.DistributionBatchRecord> DistributionBatchRecordList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"OrderDetailID"  ,strSplit)),
            //    new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributionSerialID"  ,strSplit)),
            //    new SqlParameter("@in_DistributeCounts", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributeCount"  ,strSplit)),
            //    new SqlParameter("@in_BatchRecordIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionBatchRecordList,"BatchRecordID"  ,strSplit)),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Distribution_Batch_Edit", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                for (var i = 0; i < DistributionList.Count; i++)
                {
                    var item = DistributionList[i];
                    string strsql = string.Format(@"select count(*) from TB_Distribution where warehousecount > 0 and DistributionSerialID = @in_DistributionSerialID");
                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();
                    parasnames.Add("@in_DistributionSerialID");
                    values.Add(item.DistributionSerialID);
                    if (Convert.ToInt32(h.ExecuteScalar(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray())) > 0)
                    {
                        return 2;
                    }
                }

                for (var i = 0; i < DistributionList.Count; i++)
                {
                    var item = DistributionList[i];

                    #region 判断 配送总量是否大于实际采购量，大于则回滚
                    //                    string strCheck = @"SELECT  PurchaseCount, DistributeCount
                    //                                                    FROM   TB_PurchaseOrderDetail
                    //                                                    WHERE  OrderDetailID = @in_OrderDetailID";

                    //                    DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));

                    //                    int PurchaseCount = int.Parse(dt.Rows[0]["PurchaseCount"].ToString());
                    //                    int DistributeCount = int.Parse(dt.Rows[0]["DistributeCount"].ToString());

                    //                    //判断 配送总量是否大于实际采购量，大于则回滚
                    //                    if (DistributeCount + item.DistributeCount > PurchaseCount)
                    //                    {
                    //                        new Exception("配送总量大于实际采购量");
                    //                    }
                    //                    else
                    //                    { 
                    #endregion
                    #region 更新配送记录


                    /*
                         * 更新配送单        
                         */
                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();

                    parasnames.Add("@in_OrderDetailID");
                    values.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                    parasnames.Add("@in_DistributionSerialID");
                    values.Add(string.IsNullOrEmpty(item.DistributionSerialID) ? "" : item.DistributionSerialID.Trim());

                    parasnames.Add("@in_DistributeCount");
                    values.Add(item.DistributeCount);

                    string DistributionSerialID = System.Guid.NewGuid().ToString();
                    string sql = string.Format(@"select modifytime from TB_Distribution where DistributionSerialID = '{0}'",item.DistributionSerialID);
                    DateTime time = Convert.ToDateTime(h.ExecuteScalar(sql));
                    string sqlUpdateDistribution = "";
                    if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                    {
                        sqlUpdateDistribution = @"   UPDATE TB_Distribution
		        SET    OrderDetailID = @in_OrderDetailID,
		               DistributeCount = @in_DistributeCount,
		               DistributeTime = sysdate,modifytime = sysdate,modifystatus = 1
		        WHERE  DistributionSerialID = @in_DistributionSerialID ";
                    }
                    else
                    {
                        sqlUpdateDistribution = @"   UPDATE TB_Distribution
		        SET    OrderDetailID = @in_OrderDetailID,
		               DistributeCount = @in_DistributeCount,
		               DistributeTime = sysdate
		        WHERE  DistributionSerialID = @in_DistributionSerialID ";
                    }

                   
                    h.ExecuteNonQuery(CommandType.Text, sqlUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                    #endregion

                    var item2 = DistributionBatchRecordList[i];
                    #region 更新配送批号信息

                    /*
                         * 更新配送批号        
                         */
                    string sqlUpdateDistributionBatchRecord = @" UPDATE  TB_DistributionBatchRecord
		        SET    BatchRecordID = @in_BatchRecordID
		        WHERE  DistributionSerialID = @in_DistributionSerialID";


                    List<string> parasnames2 = new List<string>();
                    List<object> values2 = new List<object>();

                    parasnames2.Add("@in_BatchRecordID");
                    values2.Add(item2.BatchRecordID);

                    parasnames2.Add("@in_DistributionSerialID");
                    values2.Add(item2.DistributionSerialID);


                    h.ExecuteNonQuery(CommandType.Text, sqlUpdateDistributionBatchRecord, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                    #endregion

                    #region 汇总采购数量

                    /*
                            * 汇总配送量        
                        */
                    string strGetSUMDistributeCount = @"SELECT nvl(SUM(nvl(DistributeCount,0)),0) SUMDistributeCount
                                                                FROM   TB_Distribution
                                                                WHERE  OrderDetailID = @in_OrderDetailID";


                    /*
                     * 更新配送量  
                     */
                    string sqlSUMDistributeCount = @" 
                                                        UPDATE TB_PurchaseOrderDetail
                                                        SET    DistributeCount = @in_DistributeCount
                                                        where OrderDetailID= @in_OrderDetailID";


                    DataTable dt2 = h.ExecuteTable(CommandType.Text, strGetSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));

                    int SUMDistributeCount = int.Parse(dt2.Rows[0]["SUMDistributeCount"].ToString());


                    List<string> parasnames3 = new List<string>();
                    List<object> values3 = new List<object>();

                    parasnames3.Add("@in_DistributeCount");
                    /*
                         * 更新配送单冗余（采购明细配送总数）  add by zdc
                         */
                    //采购明细配送总数
                    int tem_DistributeCount = 0;
                    string sqlResult = string.Format(@"SELECT SUM(DistributeCount)
                                                        FROM   TB_Distribution
                                                        WHERE  OrderDetailID = @in_OrderDetailID");
                    string Distcount3 = h.ExecuteScalar(CommandType.Text, sqlResult, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID)).ToString();
                    tem_DistributeCount = int.Parse(Distcount3);
                    string sqlTotalDistributeCount = @" 
                                                        UPDATE TB_Distribution
                                                        SET    TotalDistributeCount = @in_DistributeCount
                                                        where OrderDetailID= @in_OrderDetailID";
                    List<string> parasnames4 = new List<string>();
                    List<object> values4 = new List<object>();

                    parasnames4.Add("@in_DistributeCount");
                    values4.Add(tem_DistributeCount);

                    parasnames4.Add("@in_OrderDetailID");
                    values4.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                    h.ExecuteNonQuery(CommandType.Text, sqlTotalDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames4, values4).ToArray());
                    values3.Add(SUMDistributeCount);

                    parasnames3.Add("@in_OrderDetailID");
                    values3.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                    h.ExecuteNonQuery(CommandType.Text, sqlSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                    
                    #endregion
                    //}

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("EditPurchaseOrderDetail_Distribution_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion
        }

        /// <summary>
        /// 批量企业出库删除
        /// </summary>
        /// <param name="DistributionList">出库信息列表</param>
        /// <returns></returns>
        /// <remarks>
        /// 应只更新需要的信息。
        /// </remarks>
        public static int DeletePurchaseOrderDetail_Distribution_Batch(IList<HZ.DrugTrade.Model.Distribution> DistributionList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"OrderDetailID"  ,strSplit)),
            //    new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributionSerialID"  ,strSplit)),                          
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Distribution_Batch_Del", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                for (var i = 0; i < DistributionList.Count; i++)
                {
                    var item = DistributionList[i];

                    #region 判断 配送总量是否大于实际采购量，大于则回滚
                    string strCheck = @"SELECT  DistributeState,OrderDetailID
                                                            FROM    TB_Distribution
                                                            WHERE   DistributionSerialID = @in_DistributionSerialID";

                    DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_DistributionSerialID", item.DistributionSerialID));

                    string OrderDetailID = dt.Rows[0]["OrderDetailID"].ToString();
                    int DistributeState = int.Parse(dt.Rows[0]["DistributeState"].ToString());


                    //判断 部分配送已入库无法删除，大于则回滚
                    if (DistributeState == 1)
                    {
                        new Exception("部分配送已入库无法删除");
                    }
                    else
                    {
                    #endregion

                        #region 删除配送批号信息

                        /*
                         * 更新配送批号        
                         */
                        string sqlDelDistributionBatchRecord = @"  DELETE  FROM TB_DistributionBatchRecord
                            WHERE   DistributionSerialID = @in_DistributionSerialID ";


                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_DistributionSerialID");
                        values2.Add(item.DistributionSerialID);


                        h.ExecuteNonQuery(CommandType.Text, sqlDelDistributionBatchRecord, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                        #endregion

                        #region 删除配送记录

                        /*
                         * 删除配送单        
                         */

                            string strsql = string.Format(@"insert into TB_Distribution_DEL select * from TB_Distribution WHERE   DistributionSerialID = @in_DistributionSerialID");
                            string strsql1 = string.Format(@"update TB_Distribution_DEL set modifytime =sysdate where DistributionSerialID = @in_DistributionSerialID");
                            string sqlDelDistribution = @"  DELETE  FROM TB_Distribution
                            WHERE   DistributionSerialID = @in_DistributionSerialID";

                        

                        List<string> parasnames = new List<string>();
                        List<object> values = new List<object>();

                        parasnames.Add("@in_DistributionSerialID");
                        values.Add(string.IsNullOrEmpty(item.DistributionSerialID) ? "" : item.DistributionSerialID.Trim());
                        h.ExecuteNonQuery(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                        h.ExecuteNonQuery(CommandType.Text, strsql1, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                        h.ExecuteNonQuery(CommandType.Text, sqlDelDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                        #endregion

                        #region 汇总采购数量

                        /*
                      * 汇总配送量        
                    */
                        string strGetSUMDistributeCount = @"SELECT nvl(SUM(nvl(DistributeCount,0)),0) SUMDistributeCount
                                                                FROM   TB_Distribution
                                                                WHERE  OrderDetailID = @in_OrderDetailID";
                        /*
                         * 更新配送量  
                         */
                        string sqlSUMDistributeCount = @" 
                                                        UPDATE TB_PurchaseOrderDetail
                                                        SET    DistributeCount = @in_DistributeCount,orderdetailstate = 1
                                                        where OrderDetailID= @in_OrderDetailID";


                        DataTable dt2 = h.ExecuteTable(CommandType.Text, strGetSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));

                        int SUMDistributeCount = int.Parse(dt2.Rows[0]["SUMDistributeCount"].ToString());


                        List<string> parasnames3 = new List<string>();
                        List<object> values3 = new List<object>();

                        parasnames3.Add("@in_DistributeCount");
                        values3.Add(SUMDistributeCount);

                        parasnames3.Add("@in_OrderDetailID");
                        values3.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                        h.ExecuteNonQuery(CommandType.Text, sqlSUMDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                        /*
                        * 更新配送单冗余（采购明细配送总数）  add by zdc
                        */
                        //采购明细配送总数
                        int tem_DistributeCount = 0; 
                        string sqlResult = string.Format(@"SELECT nvl(SUM(nvl(DistributeCount,0)),0) SUMDistributeCount
                                                        FROM   TB_Distribution
                                                        WHERE  OrderDetailID = @in_OrderDetailID");
                        DataTable Distcount3 = h.ExecuteTable(CommandType.Text, sqlResult, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));
                        
                        tem_DistributeCount = int.Parse(Distcount3.Rows[0]["SUMDistributeCount"].ToString());
                        string sqlTotalDistributeCount = @" 
                                                        UPDATE TB_Distribution
                                                        SET    TotalDistributeCount = @in_DistributeCount
                                                        where OrderDetailID= @in_OrderDetailID";
                        List<string> parasnames4 = new List<string>();
                        List<object> values4 = new List<object>();

                        parasnames4.Add("@in_DistributeCount");
                        values4.Add(tem_DistributeCount);

                        parasnames4.Add("@in_OrderDetailID");
                        values4.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                        h.ExecuteNonQuery(CommandType.Text, sqlTotalDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames4, values4).ToArray());
                        //values3.Add(SUMDistributeCount);

                        //parasnames3.Add("@in_OrderDetailID");
                        //values3.Add(string.IsNullOrEmpty(item.OrderDetailID) ? "" : item.OrderDetailID.Trim());

                        //h.ExecuteNonQuery(CommandType.Text, sqlTotalDistributeCount, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                    
                        #endregion
                    }

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("DeletePurchaseOrderDetail_Distribution_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion
        }


        /// <summary>
        /// 批量更新医疗机构入库【验收】数量(0.不成功,1.成功,)
        /// </summary>
        /// <param name="distributionList">配送情况</param>
        /// <returns>0失败1成功</returns>
        public static int UpdatePurchaseOrderDetail_Warehouse_Batch(IList<HZ.DrugTrade.Model.Distribution> DistributionList, string hospitalID)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"OrderDetailID"  ,strSplit)),
                new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributionSerialID"  ,strSplit)),
                new SqlParameter("@in_WarehouseCounts", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"WarehouseCount"  ,strSplit)),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Warehouse_Update_Batch", paras);


            int i = (int)paras[paras.Length - 1].Value;
            if (i == 1)
            {
                //配送关系表从TB_DistributionRelation 变为TB_HospitalProcureCatalog
                string sql = string.Format(@"UPDATE A SET Stock = WarehouseCountSum FROM (
SELECT h.Stock,A.WarehouseCountSum FROM TB_HospitalProcureCatalog AS h INNER JOIN
(SELECT ProcureCatalogID,CompanyID_PS, SUM(WarehouseCount) AS WarehouseCountSum FROM TB_PurchaseOrderDetail A
INNER JOIN TB_PurchaseOrder p ON p.OrderID = A.OrderID
WHERE p.HospitalID = '{0}'
GROUP BY ProcureCatalogID,A.CompanyID_PS) AS A
ON A.ProcureCatalogID = h.ProcureCatalogID AND A.CompanyID_PS = h.CompanyID_PS
WHERE h.HospitalID = '{0}') A", hospitalID);
                // 刷新入库缓存
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
                return 1;
            }
            else
            {
                return i;
            }
        }

        /// <summary>
        /// 批量更新医疗机构入库【验收】数量(0.不成功,1.成功,)
        /// </summary>
        /// <param name="distributionList">配送情况</param>
        /// <returns>0失败1成功</returns>
        public static int UpdatePurchaseOrderDetail_Warehouse_Batch2(IList<HZ.DrugTrade.Model.View.Distribution> DistributionList, string hospitalID, string hospitalName, string hospitalAreaID)
        {
            /*
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] 
            { 
                new SqlParameter("@in_OrderDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"OrderDetailID"  ,strSplit)),
                new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributionSerialID"  ,strSplit)),
                new SqlParameter("@in_WarehouseCounts", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"WarehouseCount"  ,strSplit)),

                //add by hw 2010.04.12 增加批量医疗机构产品入库 存储过程的参数
                new SqlParameter("@in_ProcureCatalogIDs",HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"ProcureCatalogID",strSplit)),
                new SqlParameter("@in_CompanyPsIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"CompanyID_PS" ,strSplit)),
                new SqlParameter("@in_CompanyNamePSs",HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"CompanyName_PS",strSplit)),
                new SqlParameter("@in_HospitalIDs", hospitalID),
                new SqlParameter("@in_HospitalNames", hospitalName),
                new SqlParameter("@in_HospitalAreaIDs", hospitalAreaID),

                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Warehouse_Update_Batch", paras);


            int i = (int)paras[paras.Length - 1].Value;
            if (i == 1)
            {

                //配送关系表从TB_DistributionRelation 变为TB_HospitalProcureCatalog

                //变更统计方式，使得医疗机构产品库存与配送企业无关 by lig 2010-03-04

                string sql = string.Format(@"UPDATE A SET Stock = WarehouseCountSum FROM (
                SELECT h.Stock,A.WarehouseCountSum FROM TB_HospitalProcureCatalog AS h INNER JOIN
                (SELECT ProcureCatalogID, SUM(WarehouseCount) AS WarehouseCountSum FROM TB_PurchaseOrderDetail A
                INNER JOIN TB_PurchaseOrder p ON p.OrderID = A.OrderID
                WHERE p.HospitalID = '{0}'
                GROUP BY ProcureCatalogID) AS A
                ON A.ProcureCatalogID = h.ProcureCatalogID 
                WHERE h.HospitalID = '{0}') A", hospitalID);
                // 刷新入库缓存
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
                return 1;
            }
            else
            {
                return i;
            }
           */
            if (DistributionList == null && DistributionList.Count == 0)
            {
                return 1;
            }
            int Flag = 0;
            Oracle.DataAccess.Client.OracleConnection connection = DAL_OracleHelper.OracleConnection();
            Oracle.DataAccess.Client.OracleCommand cmd = DAL_OracleHelper.OracleCommand();
            try
            {
                //填充表参数
                TBORDERDETAILWAREHOUSE dt = new TBORDERDETAILWAREHOUSE();
                List<ORDERDETAILWAREHOUSE> NewList = new List<ORDERDETAILWAREHOUSE>();
                for (int i = 0; i < DistributionList.Count; i++)
                {
                    ORDERDETAILWAREHOUSE newobj = new ORDERDETAILWAREHOUSE();
                    newobj.ORDERDETAILID = DistributionList[i].OrderDetailID;
                    newobj.PROCURECATALOGID = DistributionList[i].ProcureCatalogID;
                    newobj.WAREHOUSECOUNT = DistributionList[i].WarehouseCount.ToString();
                    newobj.DISTRIBUTIONSERIALID = DistributionList[i].DistributionSerialID;
                    newobj.COMPANYID_PS = DistributionList[i].CompanyID_PS;
                    newobj.COMPANYNAME_PS = DistributionList[i].CompanyName_PS;
                    NewList.Add(newobj);
                }

                dt.Value = NewList.ToArray();
                //打开连接
                connection.Open();
                cmd.Connection = connection;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "usp_DetailWarehouseBatch";//存储过程名字
                //DistributionList, string hospitalID, string hospitalName, string hospitalAreaID)
                Oracle.DataAccess.Client.OracleParameter[] pars = new Oracle.DataAccess.Client.OracleParameter[5];
                pars[0] = new Oracle.DataAccess.Client.OracleParameter("V_HospitalID", hospitalID);
                pars[1] = new Oracle.DataAccess.Client.OracleParameter("V_HospitalName", hospitalName);
                pars[2] = new Oracle.DataAccess.Client.OracleParameter("V_HospitalAreaID", hospitalAreaID);
                pars[3] = new Oracle.DataAccess.Client.OracleParameter("v_TBORDERDETAILWAREHOUSE", Oracle.DataAccess.Client.OracleDbType.Object);
                pars[3].UdtTypeName = "TBORDERDETAILWAREHOUSE";
                pars[3].Value = dt;
                pars[4] = new Oracle.DataAccess.Client.OracleParameter("out_success", Oracle.DataAccess.Client.OracleDbType.Int32);
                pars[4].Direction = ParameterDirection.Output;
                cmd.Parameters.Add(pars[0]);
                cmd.Parameters.Add(pars[1]);
                cmd.Parameters.Add(pars[2]);
                cmd.Parameters.Add(pars[3]);
                cmd.Parameters.Add(pars[4]);
                cmd.ExecuteNonQuery();
                Flag = int.Parse(pars[4].Value.ToString());
            }
            catch (Exception e)
            {
                Flag = 0;
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
                connection.Dispose();
            }
            return Flag;
        }

        /// <summary>
        /// 批量更新是否开票信息(0.不成功,1.成功,)
        /// </summary>
        /// <param name="distributionList">配送情况</param>
        /// <returns>0失败1成功</returns>
        public static int UpdatePurchaseOrderDetail_Invoice_Batch(IList<HZ.DrugTrade.Model.Distribution> DistributionList)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(DistributionList,"DistributionSerialID"  ,strSplit)),
                //是否开发票
                new SqlParameter("@in_IsInvoices", HZ.Data.DAL_Helper.GetModelFieldArrayBit(DistributionList,"IsInvoice"  ,strSplit)),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_PurchaseOrderDetail_Invoice_Update_Batch", paras);

            return (int)paras[paras.Length - 1].Value;
        }

        #region 退货
        /// <summary>
        /// 批量确认退货单(0.不成功,1.成功,)
        /// </summary>
        /// <param name="returnMerchandiseList">退货单集合</param>
        /// <returns>(0.不成功,1.成功,)</returns>
        public static int ConfirmReturnMerchandise_Batch(IList<HZ.DrugTrade.Model.PurchaseOrderDetail> returnMerchandiseList)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                   new SqlParameter("@in_ReturnSerialDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(returnMerchandiseList,"ReturnSerialID"  ,strSplit)),
                 //退货状态（0未处理;1批准;2拒绝;）
                new SqlParameter("@in_ReturnState", 1),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ReturnMerchandise_ReturnState_Update_Batch", paras);

            return (int)paras[paras.Length - 1].Value;
        }

        /// <summary>
        /// 批量拒绝退货单(0.不成功,1.成功,)
        /// </summary>
        /// <param name="returnMerchandiseList">退货单集合</param>
        /// <returns>(0.不成功,1.成功,)</returns>
        public static int RefuseReturnMerchandise_Batch(IList<HZ.DrugTrade.Model.ReturnMerchandise> returnMerchandiseList)
        {
            string strSplit = ",";
            IDataParameter[] paras = new IDataParameter[] { 
                new SqlParameter("@in_ReturnSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(returnMerchandiseList,"ReturnSerialID"  ,strSplit)),
                //退货状态（0未处理;1批准;2拒绝;）
                new SqlParameter("@in_ReturnState", 2),
                new SqlParameter("@out_Success",SqlDbType.Int)
            };
            paras[paras.Length - 1].Direction = ParameterDirection.Output;

            HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ReturnMerchandise_ReturnState_Update_Batch", paras);

            return (int)paras[paras.Length - 1].Value;
        }


        #endregion

        #region 发票
        /// <summary>
        /// 添加发票信息 [同时关联配送单信息列表]
        /// </summary>
        /// <param name="invoice">发票信息 【InvoiceSerialID,InvoiceID,CompanyID_PS,HospitalID,InvoiceTime,TotalAmount必填】</param>
        /// <param name="distributionList">出库单信息列表【DistributionSerialID必填】</param>
        /// <returns></returns>
        public static int AddInvoice_Distribution(HZ.DrugTrade.Model.Invoice invoice, IList<HZ.DrugTrade.Model.Distribution> distributionList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //     new SqlParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID),
            //     new SqlParameter("@in_InvoiceID", invoice.InvoiceID),
            //     new SqlParameter("@in_TotalAmount", invoice.TotalAmount),                 
            //     new SqlParameter("@in_HospitalID", invoice.HospitalID),
            //     new SqlParameter("@in_CompanyID_PS", invoice.CompanyID_PS),
            //     new SqlParameter("@in_InvoiceTime", invoice.InvoiceTime),
            //     new SqlParameter("@in_DistributionSerialIDs",distributionList==null?"": HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionList,"DistributionSerialID"  ,strSplit)),                 
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_Add", paras);

            //return (int)paras[paras.Length - 1].Value;
            //updated by sinodier 2011年1月12日21:58:43
            #region updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string strsql = string.Format(@"select count(*) from TB_Invoice where InvoiceID = '{0}'",invoice.InvoiceID);
                if (Convert.ToInt32(h.ExecuteScalar(strsql)) > 0)
                {
                    return 2;
                }
                else
                {
                    #region 新增发票信息
                    /*
                         * 新增发票信息        
                         */
                    string DistributionSerialID = System.Guid.NewGuid().ToString();

                    string sqlInsertInvoice = @" INSERT INTO  TB_Invoice
	                                                                      (
	                                                                        InvoiceSerialID,
	                                                                        InvoiceID,
	                                                                        InvoiceTime,
	                                                                        CompanyID_PS,
	                                                                        HospitalID,
	                                                                        TotalAmount,
	                                                                        AuditTime,
	                                                                        AuditPerson，
                                                                            HospitalName,
                                                                            CompanyName_PS,
                                                                            AreaID,
                                                                            AreaName,
                                                                            MODIFYTIME,MODIFYSTATUS,UPLOADSTATUS
	                                                                      )
	                                                                      VALUES
	                                                                      (
	                                                                        @in_InvoiceSerialID,
	                                                                        @in_InvoiceID,
	                                                                        @in_InvoiceTime,
	                                                                        @in_CompanyID_PS,
	                                                                        @in_HospitalID,
	                                                                        @in_TotalAmount,
	                                                                        NULL,
	                                                                        '',
                                                                            @in_HospitalName,
                                                                            @in_CompanyName_PS,
                                                                            @in_AreaID,
                                                                            @in_AreaName,
                                                                            sysdate,0,0
	                                                                      )";

                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();


                    parasnames.Add("@in_InvoiceSerialID");
                    values.Add(string.IsNullOrEmpty(invoice.InvoiceSerialID) ? "" : invoice.InvoiceSerialID.Trim());

                    parasnames.Add("@in_InvoiceTime");
                    values.Add(invoice.InvoiceTime);

                    parasnames.Add("@in_InvoiceID");
                    values.Add(invoice.InvoiceID);

                    parasnames.Add("@in_CompanyID_PS");
                    values.Add(string.IsNullOrEmpty(invoice.CompanyID_PS) ? "" : invoice.CompanyID_PS.Trim());

                    parasnames.Add("@in_HospitalID");
                    values.Add(invoice.HospitalID);


                    parasnames.Add("@in_TotalAmount");
                    values.Add(invoice.TotalAmount ?? 0);

                    parasnames.Add("@in_HospitalName");
                    values.Add(invoice.HospitalName);

                    parasnames.Add("@in_CompanyName_PS");
                    values.Add(invoice.CompanyName_PS);

                    parasnames.Add("@in_AreaID");
                    values.Add(invoice.AreaID);

                    parasnames.Add("@in_AreaName");
                    values.Add(invoice.AreaName);

                    h.ExecuteNonQuery(CommandType.Text, sqlInsertInvoice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                    #endregion

                    if (distributionList != null && distributionList.Count > 0)
                    {
                        for (var i = 0; i < distributionList.Count; i++)
                        {
                            var item = distributionList[i];

                            #region 新增发票明细信息

                            /*
                         * 新增发票明细信息        
                         */
                            string sqlInsertInvoiceDetail = @"
	    				INSERT INTO  TB_InvoiceDetail 
	    				  (
	    					 InvoiceSerialID,
	    					 DistributionSerialID
	    				  )
	    				VALUES
	    				  (
	    					@in_InvoiceSerialID,
	    					@in_DistributionSerialID
	    				  )";


                            List<string> parasnames2 = new List<string>();
                            List<object> values2 = new List<object>();

                            parasnames2.Add("@in_DistributionSerialID");
                            values2.Add(item.DistributionSerialID);

                            parasnames2.Add("@in_InvoiceSerialID");
                            values2.Add(invoice.InvoiceSerialID);

                            h.ExecuteNonQuery(CommandType.Text, sqlInsertInvoiceDetail, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                            #endregion

                            #region 更新配送单的开票信息为已开票

                            /*
                         * 更新配送单的开票信息为已开票        
                        */
                            string strUpdateDistribution = @"
                        UPDATE TB_Distribution
	    				SET    IsInvoice = 1
	    				WHERE  DistributionSerialID = @in_DistributionSerialID	";


                            h.ExecuteNonQuery(CommandType.Text, strUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());

                            List<string> parasnames3 = new List<string>();
                            List<object> values3 = new List<object>();

                            parasnames3.Add("@in_DistributionSerialID");
                            values3.Add(string.IsNullOrEmpty(item.DistributionSerialID) ? "" : item.DistributionSerialID.Trim());

                            h.ExecuteNonQuery(CommandType.Text, strUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                            #endregion

                        }
                    }

                    h.CommitTrans();
                    retValue = 1;
                }
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("AddInvoice_Distribution", ex.Message, ex);
                retValue = 0;
            }

            return retValue;



            #endregion

        }


        /// <summary>
        /// 添加发票关联配送单信息详情信息
        /// </summary>
        ///  <param name="invoice">发票信息 【InvoiceID】</param>
        /// <param name="distributionList">出库单信息列表【DistributionSerialID必填】</param>
        /// <returns></returns>
        public static int AddInvoiceDistribution(HZ.DrugTrade.Model.Invoice invoice, IList<HZ.DrugTrade.Model.Distribution> distributionList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //     new SqlParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID),
            //     new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionList,"DistributionSerialID"  ,strSplit)),                 
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_InvoiceDistribution_Add", paras);

            //return (int)paras[paras.Length - 1].Value;
            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                for (var i = 0; i < distributionList.Count; i++)
                {
                    var item = distributionList[i];

                    #region 判断 配送总量是否大于实际采购量，大于则回滚
                    string strCheck = @"SELECT AuditStatus
                                                FROM  TB_Invoice
                                                WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                    DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID));

                    int AuditStatus = int.Parse(dt.Rows[0]["AuditStatus"].ToString());

                    string sql1 = string.Format(@"select count(*) from tb_returnmerchandisedetail where orderdetailid in (
                                                        select
                                                         orderdetailid from View_Distribution
                                                         where DistributionSerialID = '{0}') and returnstate = 0
                                                          ", item.DistributionSerialID);

                    int flag = Convert.ToInt32(h.ExecuteScalar(sql1));

                    //判断发票、的状态（是否已经提交了发票）
                    if (AuditStatus > 0)
                    {
                        return 2;
                    }
                    //产品正在退货中                
                    if (flag > 0)
                    {
                        return 3;
                    }
                }
               
                for (var i = 0; i < distributionList.Count; i++)
                {
                    var item = distributionList[i];
                    #endregion

                        string sql2 = string.Format(" Update TB_distribution set isinvoice=1 where DistributionSerialID='{0}' ", item.DistributionSerialID);
                        h.ExecuteNonQuery(sql2);
                        #region 新增发票明细信息

                        /*
                         * 新增发票明细信息        
                         */
                        string sqlInsertInvoiceDetail = @"
	    				INSERT INTO  TB_InvoiceDetail 
	    				  (
	    					 InvoiceSerialID,
	    					 DistributionSerialID,
                             MODIFYTIME,
                             MODIFYSTATUS,
                             UPLOADSTATUS
	    				  )
	    				VALUES
	    				  (
	    					@in_InvoiceSerialID,
	    					@in_DistributionSerialID,
                            sysdate,
                            0,
                            0
	    				  )";


                        List<string> parasnames1 = new List<string>();
                        List<object> values1 = new List<object>();

                        parasnames1.Add("@in_DistributionSerialID");
                        values1.Add(item.DistributionSerialID);

                        parasnames1.Add("@in_InvoiceSerialID");
                        values1.Add(invoice.InvoiceSerialID);

                        h.ExecuteNonQuery(CommandType.Text, sqlInsertInvoiceDetail, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                        #endregion



                        #region 更新发票总金额
                        string sql = string.Format(@"select  MODIFYTIME from tb_invoice where InvoiceSerialID = @in_InvoiceSerialID");
                        /*
                         * 更新发票总金额
                        */

                        List<string> parasnames3 = new List<string>();
                        List<object> values3 = new List<object>();

                        parasnames3.Add("@in_InvoiceSerialID");
                        values3.Add(string.IsNullOrEmpty(invoice.InvoiceSerialID) ? "" : invoice.InvoiceSerialID.Trim());
                        DateTime time = Convert.ToDateTime(h.ExecuteScalar(CommandType.Text, string.Format(@"select  MODIFYTIME from tb_invoice where InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray()));
                        if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                        {
                            h.ExecuteNonQuery(CommandType.Text, string.Format(@"
                         UPDATE  TB_Invoice
                            SET    TotalAmount = (
                           SELECT SUM(Price *Settlecount)
                           FROM    view_Distribution
                           WHERE  DistributionSerialID IN (SELECT 
                                                                  DistributionSerialID
                                                           FROM    view_InvoiceDistribution
                                                           WHERE  
                                                                  InvoiceSerialID = 
                                                                  @in_InvoiceSerialID)
                        ),MODIFYTIME=sysdate,MODIFYSTATUS=1
                            WHERE  InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                        }
                        else
                        {
                            h.ExecuteNonQuery(CommandType.Text, string.Format(@"
                         UPDATE  TB_Invoice
                            SET    TotalAmount = (
                           SELECT SUM(Price *Settlecount)
                           FROM    view_Distribution
                           WHERE  DistributionSerialID IN (SELECT 
                                                                  DistributionSerialID
                                                           FROM    view_InvoiceDistribution
                                                           WHERE  
                                                                  InvoiceSerialID = 
                                                                  @in_InvoiceSerialID)
                        )
                            WHERE  InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                        }
                        #endregion

                    }
                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("AddInvoiceDistribution", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion
        }


        /// <summary>
        /// 删除发票关联配送单信息详情信息
        /// </summary>
        ///  <param name="invoice">发票信息 【InvoiceID】</param>
        /// <param name="distributionList">出库单信息列表【DistributionSerialID必填】</param>
        /// <returns></returns>
        public static int DelInvoiceDistribution(HZ.DrugTrade.Model.Invoice invoice, IList<HZ.DrugTrade.Model.Distribution> distributionList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //     new SqlParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID),
            //     new SqlParameter("@in_DistributionSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(distributionList,"DistributionSerialID"  ,strSplit)),                 
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_InvoiceDistribution_Del", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                for (var i = 0; i < distributionList.Count; i++)
                {
                    var item = distributionList[i];

                    #region 判断 是否已经提交了发票
                    string strCheck = @"SELECT AuditStatus
                                                FROM  TB_Invoice
                                                WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                    DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID));

                    int AuditStatus = int.Parse(dt.Rows[0]["AuditStatus"].ToString());



                    //判断发票、的状态（是否已经提交了发票）
                    if (AuditStatus > 0)
                    {
                        retValue = 2;
                        new Exception("发票已经提交，不能删除明细");
                    }
                    else
                    {
                    #endregion

                       
                            
                        #region 删除发票明细信息
                        /*
                         * 删除发票明细信息        
                         */
                        List<string> parasnames1 = new List<string>();
                        List<object> values1 = new List<object>();

                        parasnames1.Add("@in_DistributionSerialID");
                        values1.Add(item.DistributionSerialID);

                        parasnames1.Add("@in_InvoiceSerialID");
                        values1.Add(invoice.InvoiceSerialID);

                        h.ExecuteNonQuery(CommandType.Text, string.Format(@"
	    				insert into TB_InvoiceDetail_DEL
                    select * FROM    TB_InvoiceDetail 
                    WHERE   InvoiceSerialID  = @in_InvoiceSerialID
                           AND  DistributionSerialID  = @in_DistributionSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                        h.ExecuteNonQuery(CommandType.Text, string.Format(@"
	    				UPDATE TB_InvoiceDetail_DEL
                        SET modifytime = sysdate
                    WHERE   InvoiceSerialID  = @in_InvoiceSerialID
                           AND  DistributionSerialID  = @in_DistributionSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                        h.ExecuteNonQuery(CommandType.Text, string.Format(@"
	    				DELETE 
                    FROM    TB_InvoiceDetail 
                    WHERE   InvoiceSerialID  = @in_InvoiceSerialID
                           AND  DistributionSerialID  = @in_DistributionSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                        #endregion

                        #region 更新配送单的开票信息为已开票

                        /*
                         * 更新配送单的开票信息为未开票        
                        */
                        
                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_DistributionSerialID");
                        values2.Add(item.DistributionSerialID);

                        h.ExecuteNonQuery(CommandType.Text, string.Format(@"
                        UPDATE TB_Distribution
	    				SET    IsInvoice = 0
	    				WHERE  DistributionSerialID = @in_DistributionSerialID	"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                        #endregion


                        #region 更新发票总金额

                        /*
                         * 更新发票总金额
                        */
                        
                        List<string> parasnames3 = new List<string>();
                        List<object> values3 = new List<object>();

                        parasnames3.Add("@in_InvoiceSerialID");
                        values3.Add(string.IsNullOrEmpty(invoice.InvoiceSerialID) ? "" : invoice.InvoiceSerialID.Trim());
                        DateTime time = Convert.ToDateTime(h.ExecuteScalar(CommandType.Text, string.Format(@"select  MODIFYTIME from tb_invoice where InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray()));
                        if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                        {
                            h.ExecuteNonQuery(CommandType.Text, string.Format(@"
                         UPDATE  TB_Invoice
                            SET    TotalAmount = (
                           SELECT nvl(SUM(Price *settlecount),0)
                           FROM    view_Distribution
                           WHERE  DistributionSerialID IN (SELECT 
                                                                  DistributionSerialID
                                                           FROM    view_InvoiceDistribution
                                                           WHERE  
                                                                  InvoiceSerialID = 
                                                                  @in_InvoiceSerialID)
                        ),modifytime = sysdate,modifystatus = 1
                            WHERE  InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                        }
                        else
                        {
                            h.ExecuteNonQuery(CommandType.Text, string.Format(@"
                         UPDATE  TB_Invoice
                            SET    TotalAmount = (
                           SELECT nvl(SUM(Price *settlecount),0)
                           FROM    view_Distribution
                           WHERE  DistributionSerialID IN (SELECT 
                                                                  DistributionSerialID
                                                           FROM    view_InvoiceDistribution
                                                           WHERE  
                                                                  InvoiceSerialID = 
                                                                  @in_InvoiceSerialID)
                        )
                            WHERE  InvoiceSerialID = @in_InvoiceSerialID"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                        }
                        #endregion


                    }
                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("AddInvoiceDistribution", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion


        }


        /// <summary>
        /// 删除发票【含关联配送单信息详情信息】
        /// </summary>
        ///  <param name="invoice">发票信息 【InvoiceID】</param>
        /// <returns></returns>
        public static int DelInvoice(HZ.DrugTrade.Model.Invoice invoice)
        {
            //IDataParameter[] paras = new IDataParameter[] { 
            //     new SqlParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID),               
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_Del", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();


                #region 判断 发票编号不存在
                string strCheck = @"SELECT count(1) num
		           				FROM    TB_Invoice 
		           				WHERE  InvoiceSerialID  =  @in_InvoiceSerialID";

                DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID));

                int count = int.Parse(dt.Rows[0]["num"].ToString());



                //判断发票、的状态（是否已经提交了发票）
                if (count == 0)
                {
                    retValue = 2;
                    new Exception("发票编号不存在");
                }
                else
                {
                #endregion

                #region 删除发票
                        //更新配送单的开票信息为未开票
                        string sqlUpdateDistribution = @"
	    				    UPDATE TB_Distribution
		                    SET    IsInvoice = 0
		                    WHERE  DistributionSerialID IN (SELECT DistributionSerialID
		                                                    FROM   view_InvoiceDistribution
		                                                    WHERE  InvoiceSerialID = @in_InvoiceSerialID)";

                        //删除发票明细信息
                        string sqlDelInvoiceDetail = @"
	    				   DELETE 
		                    FROM   dbo.TB_InvoiceDetail
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                        //删除发票信息
                        string sqlDelInvoice = @"
	    				    DELETE 
		                    FROM   dbo.TB_Invoice
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID ";


                        List<string> parasnames1 = new List<string>();
                        List<object> values1 = new List<object>();

                        parasnames1.Add("@in_InvoiceSerialID");
                        values1.Add(invoice.InvoiceSerialID);

                        h.ExecuteNonQuery(CommandType.Text, sqlUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                        h.ExecuteNonQuery(CommandType.Text, sqlDelInvoiceDetail, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                        h.ExecuteNonQuery(CommandType.Text, sqlDelInvoice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                        #endregion

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("DelInvoice", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

        }


        /// <summary>
        /// 删除发票_批量【含关联配送单信息详情信息】
        /// </summary>
        ///  <param name="invoiceList">发票信息列表 【InvoiceID】</param>
        /// <returns></returns>
        public static int DelInvoice_Batch(IList<HZ.DrugTrade.Model.Invoice> invoiceList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] {      
            //       new SqlParameter("@in_InvoiceSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"InvoiceSerialID"  ,strSplit)),     
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_Batch_Del", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                foreach (var item in invoiceList)
                {
                    
              
                #region 判断 发票已经提交，不能删除明细
                string strCheck = @"SELECT AuditStatus
                FROM    TB_Invoice
                WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_InvoiceSerialID", item.InvoiceSerialID));

                int AuditStatus = int.Parse(dt.Rows[0]["AuditStatus"].ToString());

                //判断发票已经提交，不能删除明细
                if (AuditStatus > 0)
                {
                    return 2;
                    //new Exception("发票已经提交，不能删除明细");
                }
                else
                {
                #endregion

                #region 删除发票
                    //更新配送单的开票信息为未开票
                    string sqlUpdateDistribution = @"
	    				    UPDATE TB_Distribution
		                    SET    IsInvoice = 0
		                    WHERE  DistributionSerialID IN (SELECT DistributionSerialID
		                                                    FROM   view_InvoiceDistribution
		                                                    WHERE  InvoiceSerialID = @in_InvoiceSerialID)";
                    string sql = string.Format(@"insert into TB_InvoiceDetail_DEL select * FROM   TB_InvoiceDetail
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID");

                    string sql1 = string.Format("update TB_InvoiceDetail_DEL set modifytime =sysdate where distributionserialid in (select distributionserialid from TB_InvoiceDetail where InvoiceSerialID = @in_InvoiceSerialID)");

                    string strsql = string.Format(@"insert into TB_Invoice_DEL select * FROM  TB_Invoice
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID");

                    string strsql1 = string.Format(@"update TB_Invoice_DEL set modifytime = sysdate WHERE  InvoiceSerialID = @in_InvoiceSerialID");
                    //删除发票明细信息
                    string sqlDelInvoiceDetail = @"
	    				   DELETE 
		                    FROM   TB_InvoiceDetail
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                    //删除发票信息
                    string sqlDelInvoice = @"
	    				    DELETE 
		                    FROM   TB_Invoice
		                    WHERE  InvoiceSerialID = @in_InvoiceSerialID ";


                    List<string> parasnames1 = new List<string>();
                    List<object> values1 = new List<object>();

                    parasnames1.Add("@in_InvoiceSerialID");
                    values1.Add(item.InvoiceSerialID);

                    h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, sql1, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, strsql1, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, sqlUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, sqlDelInvoiceDetail, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    h.ExecuteNonQuery(CommandType.Text, sqlDelInvoice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                    #endregion

                }
                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("DelInvoice_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

        }

        /// <summary>
        /// 查询发票号是否存在
        /// </summary>
        /// <returns></returns>
        public static DataTable GetPurchaseNumber(string isID, string InvoiceID)
        {
            StringBuilder sql = new StringBuilder();
            if (isID == "")
            {
                //ID为0 则是新增
                sql.Append("SELECT * FROM TB_Invoice WHERE InvoiceID = '" + InvoiceID + "' ");
            }
            else
            {
                sql.Append("SELECT * FROM TB_Invoice WHERE InvoiceID = '" + InvoiceID + "' AND InvoiceSerialID != '" + isID + "'");
            }
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql.ToString());
            return dt;
        }

        /// <summary>
        /// 修改发票信息【发票号，发票时间】
        /// </summary>
        /// <param name="invoice">发票信息 </param>
        /// <returns>(0.不成功,1.成功,2.发票编号不存在)</returns>
        public static int UpdateInvoice(HZ.DrugTrade.Model.Invoice invoice)
        {
            //IDataParameter[] paras = new IDataParameter[] { 
            //     new SqlParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID),
            //     new SqlParameter("@in_InvoiceID", invoice.InvoiceID),
            //     new SqlParameter("@in_InvoiceTime", invoice.InvoiceTime),
            //      new SqlParameter("@in_TotalAmount",invoice.TotalAmount),
            //      new SqlParameter("@in_Altertime",invoice.Altertime),
            //      new SqlParameter("@in_TotalAmount_old",invoice.TotalAmount_old),
            //     new SqlParameter("@in_CompanyID_PS",invoice.CompanyID_PS),
            //     new SqlParameter("@in_HospitalID",invoice.HospitalID),
            //     new SqlParameter("@in_Note",invoice.Note),
            //     new SqlParameter("@in_Flag",invoice.Flag),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;
            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_Update", paras);

            //return (int)paras[paras.Length - 1].Value;

            //updated by sinodier 2011年1月12日21:58:43
            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                 string strsql = string.Format(@"select count(*) from TB_Invoice where InvoiceID = '{0}'",invoice.InvoiceID);
                 if (Convert.ToInt32(h.ExecuteScalar(strsql)) > 0)
                 {
                     return 3;
                 }
                 else
                 {

                     #region 判断 发票编号不存在
                     string strCheck = @"SELECT count(1) num
		           				FROM    TB_Invoice 
		           				WHERE  InvoiceSerialID  =  @in_InvoiceSerialID";

                     DataTable dt = h.ExecuteTable(CommandType.Text, strCheck, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_InvoiceSerialID", invoice.InvoiceSerialID));

                     int count = int.Parse(dt.Rows[0]["num"].ToString());



                     //判断发票、的状态（是否已经提交了发票）
                     if (count == 0)
                     {
                         retValue = 2;
                         new Exception("发票编号不存在");
                     }
                     else
                     {
                     #endregion


                         if (invoice.Flag == 1)
                         {
                             #region 修改日志

                             /*
                             * 修改日志        
                             */
                             string sqlInsertInvoice_update_log = @"
	    				    INSERT INTO TB_Invoice_update_log(InvoiceSerialID,InvoiceID,TotalAmount,Altertime,TotalAmount_old,CompanyID_PS,HospitalID,Note) 
		    VALUES(@in_InvoiceSerialID,@in_InvoiceID,@in_TotalAmount,@in_Altertime,@in_TotalAmount_old,@in_CompanyID_PS,@in_HospitalID,@in_Note)";


                             List<string> parasnames1 = new List<string>();
                             List<object> values1 = new List<object>();

                             parasnames1.Add("@in_InvoiceSerialID");
                             values1.Add(invoice.InvoiceSerialID);

                             parasnames1.Add("@in_InvoiceID");
                             values1.Add(invoice.InvoiceID);

                             parasnames1.Add("@in_TotalAmount");
                             values1.Add(invoice.TotalAmount);

                             parasnames1.Add("@in_Altertime");
                             values1.Add(invoice.Altertime);

                             parasnames1.Add("@in_TotalAmount_old");
                             values1.Add(invoice.TotalAmount_old);

                             parasnames1.Add("@in_CompanyID_PS");
                             values1.Add(invoice.CompanyID_PS);

                             parasnames1.Add("@in_HospitalID");
                             values1.Add(invoice.HospitalID);

                             parasnames1.Add("@in_Note");
                             values1.Add(invoice.Note);


                             h.ExecuteNonQuery(CommandType.Text, sqlInsertInvoice_update_log, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());
                             #endregion
                         }

                         #region 更新发票

                         /*
                         * 更新发票        
                         */
                         string sqlUpdateInvoice = @"UPDATE TB_Invoice SET 
					InvoiceID=@in_InvoiceID,
					InvoiceTime=@in_InvoiceTime,
					TotalAmount=@in_TotalAmount
				WHERE InvoiceSerialID =  @in_InvoiceSerialID";

                         List<string> parasnames2 = new List<string>();
                         List<object> values2 = new List<object>();

                         parasnames2.Add("@in_InvoiceID");
                         values2.Add(invoice.InvoiceID);

                         parasnames2.Add("@in_InvoiceTime");
                         values2.Add(invoice.InvoiceTime);

                         parasnames2.Add("@in_TotalAmount");
                         values2.Add(invoice.TotalAmount);

                         parasnames2.Add("@in_InvoiceSerialID");
                         values2.Add(invoice.InvoiceSerialID);

                         h.ExecuteNonQuery(CommandType.Text, sqlUpdateInvoice, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());

                         #endregion
                     }

                     h.CommitTrans();
                     retValue = 1;
                 }
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("UpdateInvoice", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

        }


        /// <summary>
        /// 批量审核通过发票信息
        /// </summary>
        /// <param name="invoiceList">发票信息集合【InvoiceSerialID必填】</param>
        /// <returns></returns>
        public static int ConfirmInvoice_Batch(IList<HZ.DrugTrade.Model.Invoice> invoiceList,string[] arr)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_InvoiceSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"InvoiceSerialID"  ,strSplit)),
            //    new SqlParameter("@in_NoPassReasons", ""),
            //    new SqlParameter("@in_AuditPersons", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"AuditPerson"  ,strSplit)),                
            //    //审核状态:0未审核;1审核通过;2未通过审核
            //    new SqlParameter("@in_AuditStatus", 1),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_AuditStatus_Update_Batch", paras);

            //return (int)paras[paras.Length - 1].Value;
            //Updated by sinodier 2011年1月13日0:38:09

            #region //Updated by sinodier 2011年1月13日0:38:09
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                if (arr.Length > 0)
                {
                    for (int i = 0; i < arr.Length; i++)
                    {
                        string strsql = string.Format(@"select count(*) from tb_returnmerchandisedetail 
                    where orderdetailid in (select orderdetailid from tb_distribution where DistributionSerialID = @in_DistributionSerialID) and returnstate = 0");
                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_DistributionSerialID");
                        values2.Add(arr[i].ToString());
                        int count =Convert.ToInt32(h.ExecuteScalar(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray()));
                        if(count > 0)
                        {
                            return 2;
                        }
                    }
                }
                for (var i = 0; i < invoiceList.Count; i++)
                {
                    var item = invoiceList[i];

                    #region 确认发票回款信息

                    string sql = @" UPDATE  TB_Invoice
                SET    AuditStatus = @in_AuditStatus
                      ,NoPassReason = @in_NoPassReason
                      ,AuditPerson = @in_AuditPerson
                      ,AuditTime = sysdate
                WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();

                    parasnames.Add("@in_InvoiceSerialID");
                    values.Add(string.IsNullOrEmpty(item.InvoiceSerialID) ? "" : item.InvoiceSerialID.Trim());

                    parasnames.Add("@in_AuditStatus");
                    values.Add(1);

                    parasnames.Add("@in_NoPassReason");
                    values.Add(string.IsNullOrEmpty(item.NoPassReason) ? "" : item.NoPassReason.Trim());

                    parasnames.Add("@in_AuditPerson");
                    values.Add(string.IsNullOrEmpty(item.AuditPerson) ? "" : item.AuditPerson.Trim());


                    h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                    #endregion

                }

                #region 更新配送单的开票信息为已开票

                /*
                         * 更新配送单的开票信息为已开票        
                        */
                if (arr.Length > 0)
                {
                    for (int i = 0; i < arr.Length; i++)
                    {
                        string strUpdateDistribution = @"
                        UPDATE TB_Distribution
	    				SET    IsInvoice = 1
	    				WHERE  DistributionSerialID = @in_DistributionSerialID	";


                        List<string> parasnames2 = new List<string>();
                        List<object> values2 = new List<object>();

                        parasnames2.Add("@in_DistributionSerialID");
                        values2.Add(arr[i].ToString());

                        h.ExecuteNonQuery(CommandType.Text, strUpdateDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                    }
                }
                #endregion
  
                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("ConfirmInvoice_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue; 
            #endregion
        }
        /// <summary>
        /// 撤废发票
        /// </summary>
        /// <param name="invoiceList">发票信息集合【InvoiceSerialID必填】</param>
        /// <returns></returns>
        public static int DropInvoice_Batch(IList<HZ.DrugTrade.Model.Invoice> invoiceList)
        {
            

            #region 
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                for (var i = 0; i < invoiceList.Count; i++)
                {
                    var item = invoiceList[i];
                    string strsql = string.Format(@"select count(*) from TB_Invoice where INVOICEPAYSERIALID is not null and InvoiceSerialID = '{0}'", invoiceList[i].InvoiceSerialID);
                    if(Convert.ToInt32(h.ExecuteScalar(strsql)) > 0)
                    {
                        return 2;
                    }
                }
                for (var i = 0; i < invoiceList.Count; i++)
                {
                    var item = invoiceList[i];

                    #region 确认发票回款信息
                    string sql1 = string.Format(" UPDATE TB_Distribution SET  IsInvoice = 0 WHERE  DistributionSerialID IN (SELECT DistributionSerialID FROM   view_InvoiceDistribution WHERE  InvoiceSerialID ='{0}')", invoiceList[i].InvoiceSerialID);
                    string sql = string.Format("UPDATE TB_Invoice SET AuditStatus ={0},AuditPerson='{1}' WHERE  InvoiceSerialID ='{2}'", invoiceList[i].AuditStatus, invoiceList[i].AuditPerson, invoiceList[i].InvoiceSerialID);
                    h.ExecuteNonQuery(sql);
                    h.ExecuteNonQuery(sql1);
                    #endregion

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("DropInvoice_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion
        }
        /// <summary>
        /// 批量审核不通过发票信息
        /// </summary>
        /// <param name="invoiceList">发票信息集合【InvoiceSerialID必填】</param>
        /// <returns></returns>
        public static int NoConfirmInvoice_Batch(IList<HZ.DrugTrade.Model.Invoice> invoiceList)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_InvoiceSerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"InvoiceSerialID"  ,strSplit)),
            //     new SqlParameter("@in_NoPassReasons", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"NoPassReason"  ,strSplit)),
            //     new SqlParameter("@in_AuditPersons", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoiceList,"AuditPerson"  ,strSplit)),                
            //    //审核状态:0未审核;1审核通过;2未通过审核
            //    new SqlParameter("@in_AuditStatus", 2),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_Invoice_AuditStatus_Update_Batch", paras);

            //return (int)paras[paras.Length - 1].Value;

            //Updated by sinodier 2011年1月13日0:38:09

            #region //Updated by sinodier 2011年1月13日0:38:09
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();

                for (var i = 0; i < invoiceList.Count; i++)
                {
                    var item = invoiceList[i];

                    #region 确认发票回款信息

                    string sql = @" UPDATE  TB_Invoice
                SET    AuditStatus = @in_AuditStatus
                      ,NoPassReason = @in_NoPassReason
                      ,AuditPerson = @in_AuditPerson
                      ,AuditTime = sysdate
                WHERE  InvoiceSerialID = @in_InvoiceSerialID";

                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();

                    parasnames.Add("@in_InvoiceSerialID");
                    values.Add(string.IsNullOrEmpty(item.InvoiceSerialID) ? "" : item.InvoiceSerialID.Trim());

                    parasnames.Add("@in_AuditStatus");
                    values.Add(2);

                    parasnames.Add("@in_NoPassReason");
                    values.Add(string.IsNullOrEmpty(item.NoPassReason) ? "" : item.NoPassReason.Trim());

                    parasnames.Add("@in_AuditPerson");
                    values.Add(string.IsNullOrEmpty(item.AuditPerson) ? "" : item.AuditPerson.Trim());


                    h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                    #endregion

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("ConfirmInvoice_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;
            #endregion
        }


        /// <summary>
        /// 分页获取发票明细信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.Invoice> GetInvoiceByWhere(HZ.DrugTrade.Model.Query.Invoice where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT  * FROM View_Invoice WHERE 1=1 {0} ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Invoice>(dt);
        }


        /// <summary>
        /// 根据配送企业编号分页获取未结算发票明细信息
        /// </summary>
        /// <param name="where">查询实体【配送企业编号必填，可选填：开票时间 发票号 发票总金额】</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns>未结算发票 【开票时间 发票号 发票总金额】</returns>
        public static IList<HZ.DrugTrade.Model.View.Invoice> GetInvoiceByCompanyID_PS(Model.Query.Invoice where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT InvoiceSerialID
                                                                                                          ,InvoiceID
                                                                                                          ,InvoiceTime
                                                                                                          ,TotalAmount
                                                                                                          ,CompanyID_PS
                                                                                                          ,HospitalID
                                                                                                         FROM View_Invoice
                                                                                                         WHERE 1=1 and invoiceserialid not in (select invoiceserialid from view_invoicepay where invoicepayserialid is not null) {0} ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Invoice>(dt);
        }

        /// <summary>
        /// 分页获取发票配送明细信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.InvoiceDistribution> GetInvoiceDistributionByWhere(HZ.DrugTrade.Model.Query.InvoiceDistribution where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT  *  FROM View_InvoiceDistribution WHERE 1=1 {0} ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.InvoiceDistribution>(dt);
        }

        /// <summary>
        /// 批量确认发票回款信息
        /// </summary>
        /// <param name="invoicePayList">发票回款单信息集合【InvoicePaySerialNO必填】</param>
        /// <param name="isPass">true：通过；false：不通过</param>
        /// <returns></returns>
        public static int ConfirmInvoicePay_Batch(IList<HZ.DrugTrade.Model.InvoicePay> invoicePayList, bool isPass)
        {
            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 
            //    new SqlParameter("@in_InvoicePaySerialIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(invoicePayList,"InvoicePaySerialID"  ,strSplit)),
            //    //审核状态:0未审核;1审核通过;2未通过审核
            //    new SqlParameter("@in_PayStatus", isPass?1:2),
            //    new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_InvoicePay_PayStatus_Update_Batch", paras);

            //return (int)paras[paras.Length - 1].Value;
            //updated by sinodier 2011年1月12日21:58:43

            #region //updated by sinodier 2011年1月12日21:58:43
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                for (var i = 0; i < invoicePayList.Count; i++)
                {
                    var item = invoicePayList[i];
                    string strsql = string.Format(@"select count(*) from TB_InvoicePay WHERE InvoicePaySerialID = '{0}'",item.InvoicePaySerialID);
                    int count = Convert.ToInt32(h.ExecuteScalar(strsql));
                    if (count == 0)
                    {
                        return 2;
                    }
                }
                for (var i = 0; i < invoicePayList.Count; i++)
                {
                    var item = invoicePayList[i];

                    #region 确认发票回款信息

                    string sql = @" 
		                                UPDATE TB_InvoicePay
		                                SET    PayStatus = @in_PayStatus
		                                WHERE  InvoicePaySerialID = @in_InvoicePaySerialNO";

                    List<string> parasnames = new List<string>();
                    List<object> values = new List<object>();

                    parasnames.Add("@in_PayStatus");
                    values.Add(isPass ? 1 : 0);

                    parasnames.Add("@in_InvoicePaySerialNO");
                    values.Add(string.IsNullOrEmpty(item.InvoicePaySerialID) ? "" : item.InvoicePaySerialID.Trim());

                    h.ExecuteNonQuery(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames, values).ToArray());
                    #endregion

                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("ConfirmInvoicePay_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue; 
            #endregion
        }


        /// <summary>
        /// 分页获取未回款发票明细信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.InvoicePay> GetInvoiceNoPayByWhere(HZ.DrugTrade.Model.Query.InvoicePay where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT  * FROM View_InvoicePay  WHERE InvoicePaySerialID IS NULL  {0} ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.InvoicePay>(dt);
        }

        /// <summary>
        /// 分页获取已回款发票明细信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.InvoicePay> GetInvoicePayByWhere(HZ.DrugTrade.Model.Query.InvoicePay where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, true);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT  * FROM View_InvoicePay  WHERE 1=1 {0} ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.InvoicePay>(dt);
        }


        /// <summary>
        /// 分页获取已回款发票汇总信息
        /// </summary>
        /// <param name="where">查询实体</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">页面大小</param>
        /// <param name="totalrecord">记录总数</param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.InvoicePay> GetTotalInvoicePayByWhere(HZ.DrugTrade.Model.Query.InvoicePay where, int currentpage, int pagesize, out int totalrecord)
        {
            string condition = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where, false);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format(@"SELECT  CompanyID_PS,
 CompanyName_PS,
 max(InvoicePayTime) InvoicePayTime,
 max(PayAmount) PayAmount,
 max(ActualAmount) ActualAmount,
 max(InvoicePayName) InvoicePayName,
 max(InvoicePayNo) InvoicePayNo,
 max(HospitalName) HospitalName,
 AreaID,
 HospitalID,
 InvoicePaySerialID,
 NVL(max(PayStatus), 0) AS PayStatus,
 max(Remarks) Remarks FROM View_InvoicePay  WHERE InvoicePaySerialID IS NOT NULL {0}  group by InvoicePaySerialID,CompanyID_PS,CompanyName_PS,AreaID,HospitalID order by InvoicePayTime DESC ", condition), pagesize);
            pdes.CurrentPage = currentpage;
            totalrecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.InvoicePay>(dt);
        }


        #endregion


        /// <summary>
        /// 批量拒绝退货单(0.不成功,1.成功,)
        /// </summary>
        /// <param name="returnMerchandiseList">退货单集合</param>
        /// <returns>(0.不成功,1.成功,)</returns>
        public static int RefuseReturnMerchandise_Batch(IList<HZ.DrugTrade.Model.ReturnMerchandiseDetail> list)
        {

            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                foreach (var item in list)
                {
                    string strsql = string.Format(@"select count(*) from TB_ReturnMerchandiseDetail where ReturnState = 1 and ReturnSerialDetailID = '{0}'", item.ReturnSerialDetailID);
                    if (Convert.ToInt32(h.ExecuteScalar(strsql)) > 0)
                    {
                        return 2;
                    }
                }
                foreach (var item in list)
                {
                    h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetUpdateSql(item));
                    string sql = string.Format("update Tb_Purchaseorderdetail set returncount=0 where OrderDetailID in (select OrderDetailID from TB_returnmerchandisedetail  where ReturnSerialDetailID='{0}')", item.ReturnSerialDetailID);
                    h.ExecuteNonQuery(sql);
                }
                h.CommitTrans();
            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_PurchaseOrderDetail", "拒绝退货单", ee);
                h.RollTrans();
                return 0;
            }
            return 1;
        }

        /// <summary>
        /// 批量确认退货单(0.不成功,1.成功,)
        /// </summary>
        /// <param name="returnMerchandiseList">退货单集合</param>
        /// <returns>(0.不成功,1.成功,)</returns>
        public static int ConfirmReturnMerchandise_Batch(IList<HZ.DrugTrade.Model.ReturnMerchandiseDetail> returnMerchandiseList)
        {

            //string strSplit = ",";
            //IDataParameter[] paras = new IDataParameter[] { 

            //       new SqlParameter("@in_ReturnSerialIDs",HZ.Data.DAL_Helper.GetModelFieldArrayString(returnMerchandiseList,"ReturnSerialID"  ,strSplit)),
            //       new SqlParameter("@in_ReturnSerialDetailIDs", HZ.Data.DAL_Helper.GetModelFieldArrayString(returnMerchandiseList,"ReturnSerialDetailID"  ,strSplit)),
            //       new SqlParameter("@out_Success",SqlDbType.Int)
            //};
            //paras[paras.Length - 1].Direction = ParameterDirection.Output;

            //HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_ReturnMerchandise_ReturnState_Confirm_Batch", paras);

            //return (int)paras[paras.Length - 1].Value;

            /*
             * 批量批准退货单，同时生成一个采购量为负数的采购单用来冲抵
             * 
             * 
             */


            //updated by sinodier 2011年1月13日1:33:39
            #region //updated by sinodier 2011年1月13日1:33:39
            int retValue = 0;
            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                foreach (var item in returnMerchandiseList)
                {
                    string strsql = string.Format(@"select count(*) from TB_ReturnMerchandiseDetail where ReturnState = 2 and ReturnSerialDetailID = '{0}'",item.ReturnSerialDetailID);
                    if(Convert.ToInt32(h.ExecuteScalar(strsql)) > 0 )
                    {
                        return 2;
                    }
                }
                foreach (var item in returnMerchandiseList)
                {

                    /*
                     * 根据退货单 生成一个采购单
                     * 
                     * 01插入跟退货单ID号相同的采购单
                     * 02插入采购单明细
                     * 03插入配送明细
                     * 04更新退货单明细
                     * 05更新采购单采购金额
                     * 06更新医疗机构产品某配送企业的库存
                     * 07更新医疗机构产品总库存
                     * 
                     * 
                     */

                    string strReturnMerchandise = @"SELECT  HospitalID,
		                ReturnName,
		                CreateUserID
		                FROM   TB_ReturnMerchandise
		                WHERE  ReturnSerialID = @in_ReturnSerialID";

                    DataTable dt = h.ExecuteTable(CommandType.Text, strReturnMerchandise, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_ReturnSerialID", item.ReturnSerialID));

                    string HospitalID =  dt.Rows[0]["HospitalID"].ToString() ;
                    string ReturnName = dt.Rows[0]["ReturnName"].ToString() + "(退货单)" + Guid.NewGuid().ToString().Substring(0, 3);//构造的采购单名称这样才不重复
                    string CreateUserID =  dt.Rows[0]["CreateUserID"].ToString() ;
                   
                    //查出要插入采购单 明细里的冗余字段信息 add by zdc
                    string strRongYu = string.Format(@"select H.HospitalName,H.AreaID,A.AreaName 
                                        from TB_Hospital H,TB_Area A 
                                        where  HospitalID=@hospitalID and H.AreaID=A.AreaID");
                    DataTable dtRY = h.ExecuteTable(CommandType.Text, strRongYu, HZ.Data.DBParameterFactory.GetIDbDataParameter("@hospitalID", HospitalID));

                    string hospitalName = dtRY.Rows[0]["HospitalName"].ToString();
                    string areaID = dtRY.Rows[0]["AreaID"].ToString();
                    string areaName = dtRY.Rows[0]["AreaName"].ToString();
                    //找配送企业名称 冗余字段 add by zdc
                    string comName = string.Format("select companyname from TB_Company where companyid='"+item.CompanyID+"'");
                    string companyName = HZ.Data.DbHelperFactory.Create().ExecuteScalar(comName).ToString();

                    string strReturnMerchandiseDetail = @"SELECT  ProcureCatalogID,
		                CompanyID,
		                ReturnCount,orderdetailid
		                FROM   TB_ReturnMerchandiseDetail
		                WHERE  ReturnSerialDetailID = @in_ReturnSerialDetailID";

                    DataTable dt2 = h.ExecuteTable(CommandType.Text, strReturnMerchandiseDetail, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_ReturnSerialDetailID", item.ReturnSerialDetailID));

                    string ProcureCatalogID = dt2.Rows[0]["ProcureCatalogID"].ToString();
                    string CompanyID = dt2.Rows[0]["CompanyID"].ToString();
                    int ReturnCount =int.Parse(dt2.Rows[0]["ReturnCount"].ToString());
                    string purchaseorderdetailid = dt2.Rows[0]["orderdetailid"].ToString();
                    #region 插入采购单
                    /*
                     * --插入采购单
                     */
                    string strInsertPurchaseOrder = @"INSERT INTO TB_PurchaseOrder
		              (
		                OrderID,
		                HospitalID,
		                OrderName,
		                TotalAmount,
		                StartTime,
		                PlanPerson,
		                PlanTime,
		                OrderStatus,
		                Remarks,
                        MODIFYTIME,MODIFYSTATUS,UPLOADSTATUS
		              )
		            VALUES
		              (
		                @in_ReturnSerialID,
		                @in_HospitalID,
		                @in_ReturnName,
		                '0',
		                sysdate,
		                @in_CreateUserID,
		                sysdate,
		                '1',
		                '退货冲抵',
                        sysdate,0,0
		              )";


                    List<string> parasnames1 = new List<string>();
                    List<object> values1 = new List<object>();

                    parasnames1.Add("@in_ReturnSerialID");
                    values1.Add(item.ReturnSerialDetailID);//构造的采购单号这样才不重复

                    parasnames1.Add("@in_HospitalID");
                    values1.Add(HospitalID);

                    parasnames1.Add("@in_ReturnName");
                    values1.Add(ReturnName);

                    parasnames1.Add("@in_CreateUserID");
                    values1.Add(CreateUserID);

                    h.ExecuteNonQuery(CommandType.Text, strInsertPurchaseOrder, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames1, values1).ToArray());

                    
                    #endregion


                    #region 插入采购明细
                    /*
                     * --插入采购明细
                     */
                    string OrderDetailID = System.Guid.NewGuid().ToString();
                    string strInsertPurchaseOrderDetail = string.Format(@" INSERT INTO TB_PurchaseOrderDetail
		                                                      (
		                                                        OrderDetailID,
		                                                        FlowID,
		                                                        OrderID,
		                                                        CompanyID_PS,
		                                                        ProcureCatalogID,
		                                                        CurrentNode,
		                                                        PurchaseCount,
		                                                        Price,
		                                                        Amount,
		                                                        ExecuteTime,
		                                                        OrderDetailState,
		                                                        DistributeCount,
		                                                        WarehouseCount,
                                                                HospitalID,
                                                                HospitalName,
                                                                AreaID,
                                                                AreaName,
                                                                CompanyName_PS,
                                                                MODIFYTIME,MODIFYSTATUS,UPLOADSTATUS
		                                                      )
		                                                    SELECT '{0}',
		                                                           '1',
		                                                           @in_ReturnSerialID,
		                                                           CompanyID,
		                                                           ProcureCatalogID,
		                                                           '01',
		                                                           -ReturnCount,
		                                                           Orginalprice,
		                                                           cast(RetrunPrice as NUMBER(18,3)) * cast(-ReturnCount as integer),
		                                                           Sysdate,
		                                                           '2',
		                                                           -ReturnCount,
		                                                           -ReturnCount,
                                                                    '{1}',
                                                                    '{2}',
                                                                    '{3}',
                                                                    '{4}',
                                                                    '{5}',
                                                                    sysdate,0,0
		                                                    FROM   TB_ReturnMerchandiseDetail
		                                                    WHERE  ReturnSerialDetailID = @in_ReturnSerialDetailID", OrderDetailID,HospitalID,hospitalName,areaID,areaName,companyName);

                    List<string> parasnames2 = new List<string>();
                    List<object> values2 = new List<object>();

                    parasnames2.Add("@in_ReturnSerialID");
                    values2.Add(item.ReturnSerialDetailID);

                    parasnames2.Add("@in_ReturnSerialDetailID");
                    values2.Add(item.ReturnSerialDetailID);

                    h.ExecuteNonQuery(CommandType.Text, strInsertPurchaseOrderDetail, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames2, values2).ToArray());
                    
                    #endregion



                    #region 插入配送明细 
                    /*
                     * --插入配送明细 插入冗余的字段 edit by zdc
                     */
                    string DistributionSerialID = System.Guid.NewGuid().ToString();
                    string strInsertDistribution = string.Format(@"  INSERT INTO TB_Distribution
		                                                                  (
		                                                                    DistributionSerialID,
		                                                                    OrderDetailID,
		                                                                    DistributeCount,
		                                                                    DistributeTime,
		                                                                    DistributeState,
		                                                                    WarehouseCount,
		                                                                    WarehouseTime,
		                                                                    IsInvoice,
                                                                        HospitalID,
                                                                        HospitalName,
                                                                        CompanyID_PS,
                                                                        CompanyName_PS,
                                                                        ProcureCatalogID,
                                                                        BatchRecordID,
                                                                        AreaID,
                                                                        AreaName,
                                                                        OrderID,
                                                                        PurchaseCount,
                                                                        Price,
                                                                        OrderDetailState,
                                                                        TotalWarehouseCount,
                                                                        TotalDistributeCount,
                                                                        MODIFYTIME,MODIFYSTATUS,UPLOADSTATUS
		                                                                  )
		                                                                SELECT '{0}',
		                                                                       '{1}',
		                                                                       -ReturnCount,
		                                                                       sysdate,
		                                                                       '1',
		                                                                       -ReturnCount,
		                                                                       sysdate,
		                                                                       '0',
                                                                            '{2}',
                                                                            '{3}',
                                                                            CompanyID,
                                                                            '{4}',
                                                                            ProcureCatalogID,
                                                                            BatchrecordID,
                                                                            '{5}',
                                                                            '{6}',
                                                                            '{7}',
                                                                            -ReturnCount,
                                                                            RetrunPrice,
                                                                            '2',
                                                                            -ReturnCount,
                                                                            -ReturnCount,
                                                                            sysdate,0,0
                                                                        FROM   TB_ReturnMerchandiseDetail
		                                                                WHERE  ReturnSerialDetailID = @in_ReturnSerialDetailID", DistributionSerialID, OrderDetailID,
                                                                         HospitalID, hospitalName, companyName, areaID, areaName, item.ReturnSerialID);
                    
                    List<string> parasnames3 = new List<string>();
                    List<object> values3 = new List<object>();

                    parasnames3.Add("@in_ReturnSerialDetailID");
                    values3.Add(item.ReturnSerialDetailID);

                    h.ExecuteNonQuery(CommandType.Text, strInsertDistribution, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames3, values3).ToArray());
                    

                    #endregion


                    #region 更新退货单明细
                    /*
                     *  --更新退货单明细                     
                     */
                    string strUpdateReturnMerchandiseDetail = @" UPDATE TB_ReturnMerchandiseDetail
		                                                        SET    ReturnState = '1'
		                                                        WHERE  ReturnSerialDetailID = @in_ReturnSerialDetailID ";


                    h.ExecuteNonQuery(CommandType.Text, strUpdateReturnMerchandiseDetail, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_ReturnSerialDetailID", item.ReturnSerialDetailID));


                    #endregion



                    #region 更新采购单总金额
                    /*
                     *  --更新采购单总金额                     
                     */
                    string strUpdateTB_PurchaseOrder = @" UPDATE  TB_PurchaseOrder
		                                                                 SET    TotalAmount = (SELECT SUM(Amount)
		                                                                                        FROM   TB_PurchaseOrderDetail
		                                                                                        WHERE  OrderID = @in_ReturnSerialID)
		                                                                 WHERE  OrderID = @in_ReturnSerialID ";
                    List<string> parasnames4= new List<string>();
                    List<object> values4 = new List<object>();

                    parasnames4.Add("@in_ReturnSerialID");
                    values4.Add(item.ReturnSerialDetailID);

                    h.ExecuteNonQuery(CommandType.Text, strUpdateTB_PurchaseOrder, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames4, values4).ToArray());
                    

                    #endregion
                    #region 更新采购单明细可结算数量
                    /*
                     *  --更新采购单明细可结算数量                     
                     */
                    string strsql = string.Format(@"select (nvl(warehousecount, 0) - nvl(hasreturncount, 0)) SettleCount,a.ORDERDETAILID
                                                    from tb_purchaseorderdetail a
                                                    left join (select orderdetailid, sum(returncount) hasreturncount
                                                                from tb_returnmerchandisedetail
                                                               where returnstate = 1 group by orderdetailid) b
                                                      on a.orderdetailid = b.orderdetailid
                                                   where purchasecount > 0
                                                     and a.orderdetailid =  @in_OrderDetailID");
                    int settlecount = Convert.ToInt32(h.ExecuteScalar(CommandType.Text, strsql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID)));
                    string strUpdateSettleCount = string.Format(@" UPDATE tb_purchaseorderdetail
		                                                        SET    SettleCount = {0}
                                         where orderdetailid = @in_OrderDetailID", settlecount);


                    h.ExecuteNonQuery(CommandType.Text, strUpdateSettleCount, HZ.Data.DBParameterFactory.GetIDbDataParameter("@in_OrderDetailID", item.OrderDetailID));


                    #endregion

                    #region 更新医疗机构产品某配送企业的库存
                    /*
                    *  --更新医疗机构产品某配送企业的库存                     
                    */
                    string strUpdateWarehouseCountSum = @" update tb_hosprocurecatalogcomps A
                set Stock =(
                SELECT WarehouseCountSum FROM(
                SELECT X.ProcureCatalogID,X.HospitalID,X.CompanyID_PS,h.Stock,
                                  X.WarehouseCountSum,X.price
                           FROM   tb_hosprocurecatalogcomps  h
                                  INNER JOIN (
                                           SELECT ProcureCatalogID,
                                                  p.HospitalID,
                                                  CompanyID_PS,
                                                  SUM(WarehouseCount)  
                                                  WarehouseCountSum,price
                                           FROM  tb_purchaseorderdetail A
                                                  INNER JOIN  tb_purchaseorder p
                                                       ON  p.OrderID = A.OrderID
                                           GROUP BY
                                                  ProcureCatalogID,
                                                  p.HospitalID,
                                                  CompanyID_PS  ,price
                                       )  X
                                       ON  X.ProcureCatalogID = h.ProcureCatalogID
                                       AND X.HospitalID = h.HospitalID
                                       AND X.CompanyID_PS = h.CompanyPsID
                                       AND X.HospitalID =@in_HospitalID
                                       AND X.CompanyID_PS =@in_CompanyID_PS
                                       AND X.price = h.price
                                       and h.Stock <> WarehouseCountSum
                                                                                             
                                       ) B
                                       WHERE A.ProcureCatalogID = B.ProcureCatalogID
                                       AND A.HospitalID = B.HospitalID
                                       AND A.CompanyPsID = B.CompanyID_PS 
                                       AND A.Price=B.price 
                                            )
            WHERE EXISTS(
                                    SELECT WarehouseCountSum FROM(
                                      SELECT X.ProcureCatalogID,X.HospitalID,X.CompanyID_PS,h.Stock,
                                  X.WarehouseCountSum,X.price
                           FROM   tb_hosprocurecatalogcomps  h
                                  INNER JOIN (
                                           SELECT ProcureCatalogID,
                                                  p.HospitalID,
                                                  CompanyID_PS,
                                                  SUM(WarehouseCount)  
                                                  WarehouseCountSum,price
                                           FROM  tb_purchaseorderdetail A
                                                  INNER JOIN  tb_purchaseorder p
                                                       ON  p.OrderID = A.OrderID
                                                       
                                           GROUP BY
                                                  ProcureCatalogID,
                                                  p.HospitalID,
                                                  CompanyID_PS  ,price
                                       )  X
                                       ON  X.ProcureCatalogID = h.ProcureCatalogID
                                       AND X.HospitalID = h.HospitalID
                                       AND X.CompanyID_PS = h.CompanyPsID
                                       AND X.HospitalID =@in_HospitalID
                                       AND X.CompanyID_PS = @in_CompanyID_PS
                                       AND X.price = h.price
                                       and h.Stock <> WarehouseCountSum
                                       ) B
                                                               WHERE A.ProcureCatalogID = B.ProcureCatalogID
                                                           AND A.HospitalID = B.HospitalID
                                                           AND A.CompanyPsID = B.CompanyID_PS
                                                           AND A.price=B.price
                                    ) ";


                    List<string> parasnames5 = new List<string>();
                    List<object> values5 = new List<object>();

                    parasnames5.Add("@in_CompanyID_PS");
                    values5.Add(CompanyID);

                    parasnames5.Add("@in_HospitalID");
                    values5.Add(HospitalID);
                    h.ExecuteNonQuery(CommandType.Text, strUpdateWarehouseCountSum, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames5, values5).ToArray());
                    
                    #endregion

                    #region 更新医疗机构产品总库存
                    /*
                    *  --更新医疗机构产品总库存                     
                    */
                    string strUpdateHospitalWarehouseCountSum = @"UPDATE TB_HospitalProcureCatalog A
                              SET    Stock = (    
                                  SELECT WarehouseCountSum FROM 
                                  (
                                  SELECT A.ProcureCatalogID,A.HospitalID,h.Stock,
                                                    A.WarehouseCountSum,h.price 
                                             FROM    TB_HospitalProcureCatalog  h
                                                    INNER JOIN (
                                                             SELECT ProcureCatalogID,
                                                                    p.HospitalID,
                                                                    SUM(WarehouseCount)   
                                                                    WarehouseCountSum,A.Price
                                                             FROM   TB_PurchaseOrderDetail A
                                                                    INNER JOIN   TB_PurchaseOrder p
                                                                         ON  p.OrderID = A.OrderID
                                                             GROUP BY
                                                                    ProcureCatalogID,
                                                                    p.HospitalID,price
                                                         )   A
                                                         ON  A.ProcureCatalogID = h.ProcureCatalogID
                                                         AND A.HospitalID = h.HospitalID
                                                         AND A.Price=h.price
                                                         AND A.HospitalID = @in_HospitalID
                                                         AND h.Stock <> WarehouseCountSum
                                    ) B WHERE A.ProcureCatalogID = B.ProcureCatalogID
                                                 AND A.HospitalID = B.HospitalID
                                                 AND A.Price=B.price)
                                  WHERE  EXISTS( 
                                  SELECT WarehouseCountSum FROM 
                                  (
                                  SELECT A.ProcureCatalogID,A.HospitalID,h.Stock,
                                                    A.WarehouseCountSum,A.price
                                             FROM    TB_HospitalProcureCatalog  h
                                                    INNER JOIN (
                                                             SELECT ProcureCatalogID,
                                                                    p.HospitalID,
                                                                    SUM(WarehouseCount)   
                                                                    WarehouseCountSum,price
                                                             FROM   TB_PurchaseOrderDetail A
                                                                    INNER JOIN   TB_PurchaseOrder p
                                                                         ON  p.OrderID = A.OrderID
                                                             GROUP BY
                                                                    ProcureCatalogID,
                                                                    p.HospitalID,price
                                                         )   A
                                                         ON  A.ProcureCatalogID = h.ProcureCatalogID
                                                         AND A.HospitalID = h.HospitalID
                                                         AND A.Price=h.price
                                                         AND A.HospitalID = @in_HospitalID
                                                         AND h.Stock <> WarehouseCountSum
                                    ) B WHERE A.ProcureCatalogID = B.ProcureCatalogID
                                                 AND A.HospitalID = B.HospitalID
                                                 AND A.Price=B.Price
                                  )  ";

                    List<string> parasnames6 = new List<string>();
                    List<object> values6 = new List<object>();

                    parasnames6.Add("@in_HospitalID");
                    values6.Add(HospitalID);
                    h.ExecuteNonQuery(CommandType.Text, strUpdateHospitalWarehouseCountSum, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames6, values6).ToArray());
                    
                    #endregion
                    #region 更新发票总金额
                    /*
                     * 更新发票总金额  add by Meng 2013-01-18
                     * */
                    if (!string.IsNullOrEmpty(purchaseorderdetailid))
                    {
                        string sqlstr = string.Format(@"select count(*) from view_invoicedistribution where  orderdetailid = @in_purchaseorderdetailid");
                        List<string> parasnames9 = new List<string>();
                        List<object> values9 = new List<object>();
                        parasnames9.Add("in_purchaseorderdetailid");
                        values9.Add(purchaseorderdetailid);

                        int countinvoice = Convert.ToInt32(h.ExecuteScalar(CommandType.Text, sqlstr, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames9, values9).ToArray()));
                        if (countinvoice > 0)
                        {

                            string sql = string.Format(@"select sum(settleamount) settleamounttotal from  view_invoicedistribution where invoiceid in (
select invoiceid from view_invoicedistribution where orderdetailid = @in_purchaseorderdetailid)");
                            List<string> parasnames7 = new List<string>();
                            List<object> values7 = new List<object>();
                            parasnames7.Add("in_purchaseorderdetailid");
                            values7.Add(purchaseorderdetailid);

                            string amount = h.ExecuteScalar(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames7, values7).ToArray()).ToString();
                            List<string> parasnames8 = new List<string>();
                            List<object> values8 = new List<object>();
                            parasnames8.Add("in_purchaseorderdetailid");
                            values8.Add(purchaseorderdetailid);
                            DateTime time = Convert.ToDateTime(h.ExecuteScalar(CommandType.Text, string.Format(@"select MODIFYTIME from tb_invoice where invoiceid in(select invoiceid from view_invoicedistribution where orderdetailid = @in_purchaseorderdetailid)"), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames8, values8).ToArray()));
                            if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                            {
                                h.ExecuteNonQuery(CommandType.Text, string.Format(@"update tb_invoice set totalamount = '{0}',MODIFYTIME=sysdate,MODIFYSTATUS=1  where invoiceid in(select invoiceid from view_invoicedistribution where orderdetailid = @in_purchaseorderdetailid)", amount), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames8, values8).ToArray());
                            }
                            else
                            {
                                h.ExecuteNonQuery(CommandType.Text, string.Format(@"update tb_invoice set totalamount = '{0}'  where invoiceid in(select invoiceid from view_invoicedistribution where orderdetailid = @in_purchaseorderdetailid)", amount), HZ.Data.DBParameterFactory.GetIDbDataParameterList(parasnames8, values8).ToArray());
                            }
                        }
                    }
                    #endregion
                }

                h.CommitTrans();
                retValue = 1;
            }
            catch (Exception ex)
            {
                h.RollTrans();
                //执行不成功，记录日志 
                HZ.Common.log4netManager.LogDebug("ConfirmReturnMerchandise_Batch", ex.Message, ex);
                retValue = 0;
            }

            return retValue;


            #endregion

        }
    }
}
